import { Injectable } from '@angular/core';
import { Storage } from '@ionic/storage';
import { SQLite, SQLiteObject } from '@ionic-native/sqlite';
import { Http, Headers } from '@angular/http';
import { Transfer, TransferObject } from '@ionic-native/transfer';
import { File } from '@ionic-native/file';
import { Zip } from '@ionic-native/zip';
import { BarcodeScanner } from '@ionic-native/barcode-scanner';
import { TranslateService } from '@ngx-translate/core';
import { $WebSocket } from 'angular2-websocket/angular2-websocket';
import { ModalController } from 'ionic-angular';

import { AppConfig } from '../app/app.config';
import { GlobalData } from '../providers/GlobalData';
import { HelperService } from './services.helper';
import { ServerQrCodePage } from '../pages/server-qr-code/server-qr-code';

declare const cordova: any;
declare var QRious: any;

@Injectable()
export class OfflineService {

  secondPath: string = 'product-media/';
  pdfPath: string = '~/Documents/invoice.pdf';
  db: SQLiteObject;
  activate: any;
  user: any;
  fileTransfer: TransferObject = this.transfer.create();

  constructor(
    private storage: Storage,
    private sqlite: SQLite,
    private helperService: HelperService,
    private http: Http,
    private transfer: Transfer,
    private file: File,
    private zip: Zip,
    private global: GlobalData,
    private translate: TranslateService,
    private modalCtrl: ModalController,
    private barcodeScanner: BarcodeScanner) {

    this.storage.get('activate').then((activate) => {
      this.activate = activate;
    });

    this.storage.get('user').then((user) => {
      this.user = user;
    });

  }

  serialize(obj, prefix?) {
    var str = [];
    for (var p in obj) {
      if (obj.hasOwnProperty(p)) {
        var k = prefix ? prefix + '[' + p + ']' : p, v = obj[p];
        str.push(typeof v == 'object' ? this.serialize(v, k) : encodeURIComponent(k) + '=' + encodeURIComponent(v));
      }
    }
    return str.join('&');
  }

  checkUndefined(params) {
    Object.keys(params).map(function (objectKey, index) {
      var value = params[objectKey];
      if (typeof value == 'undefined' || value == null) {
        params[objectKey] = '';
      }
    });
  }

  emptyTozero(str) {
    return str != '' ? str : 0;
  }

  encodePreSku(json_array) {
    var sku = '';
    var spec_array = JSON.parse(json_array);
    for (var i = 0; i < spec_array.length; i++) {
      sku = sku + 'X' + spec_array[i].dictionary.toString(16);
      sku = sku + 'Y' + spec_array[i].option.toString(16);
      sku = sku + '-';
    }
    return sku;
  }

  getUrl() {
    return new Promise<string>(resolve => {
      this.storage.get('activate').then((activate) => {
        let url: string;
        if (this.global.curEnvir == 'SANDBOX_MODE') {
          url = AppConfig.getServerHttp() + this.activate.prefix + this.activate.path + AppConfig.getSubDomin() + AppConfig.getApiPath();
        } else {
          url = AppConfig.getServerHttp() + this.activate.path + AppConfig.getSubDomin() + AppConfig.getApiPath();
        }
        resolve(url);
      });
    });
  }

  /**************************************************
  // get offline data
  **************************************************/
  getOfflineData(params) {
    this.helperService.showLoading();
    return new Promise((resolve, reject) => {
      this.getUrl().then(url => {
        let uri = encodeURI(url);
        let headers = new Headers();
        headers.append('Content-Type', 'application/x-www-form-urlencoded');
        this.http.post(uri + '/get-offline-data', this.serialize(params), { headers: headers })
          .map(res => res.json())
          .subscribe(data => {
            if (data.status == 'Y') {
              this.sqlite.create({
                name: this.activate.prefix + this.activate.path + '.db',
                location: 'default'
              }).then((db: SQLiteObject) => {
                db.sqlBatch(data.data).then(data => {
                  console.log('sql batch completed');
                  this.helperService.hideLoading();
                  resolve();
                }, (err) => {
                  console.log('batch sql fail' + err);
                  this.helperService.hideLoading();
                  reject(err);
                });
              }, (err) => {
                console.log('sqlite create fail' + err);
                this.helperService.hideLoading();
                reject(err);
              });
            }
            else {
              console.log('api return status E' + data.msg);
              this.helperService.hideLoading();
              reject(data.msg);
            }
          }, (err) => {
            console.log('api get offline data error' + err);
            this.helperService.hideLoading();
            reject(err);
          });
      });
    });
  }

  /**************************************************
  // get offline photo
  **************************************************/
  getOfflinePhoto(params) {
    this.helperService.showLoading();
    return new Promise((resolve, reject) => {
      this.getUrl().then(url => {
        let targetPath = this.file.dataDirectory + this.secondPath + 'photo.zip';
        let unzipPath = this.file.dataDirectory + this.secondPath;
        this.fileTransfer.onProgress((progress) => {
          console.log('downloading' + Math.round((progress.loaded / progress.total) * 100) + '%');
        });
        this.fileTransfer.download(url, targetPath, true).then((entry) => {
          this.zip.unzip(targetPath, unzipPath, (progress) => console.log('unzipping' + Math.round((progress.loaded / progress.total) * 100) + '%'))
            .then((result) => {
              if (result === 0) {
                console.log('unzip complete successfully');
                this.helperService.hideLoading();
                resolve();
              }
              if (result === -1) {
                console.log('unzip complete with error');
                this.helperService.hideLoading();
                reject(result);
              }
            }, (err) => {
              console.log('unzip fail' + err);
              this.helperService.hideLoading();
              reject(err);
            });
        }, (err) => {
          console.log('download photo fail' + err);
          this.helperService.hideLoading();
          reject(err);
        });
      });
    });
  }


  /**************************************************
  // offline: getCategoryNestedData
  **************************************************/
  getCategoryNestedData(category, locale) {
    return new Promise((resolve, reject) => {
      var data = {
        id: category.ID,
        type: category.TYPE,
        parent_id: category.PARENT_ID,
        name: category['NAME_' + locale],
        name_en_us: category.NAME_EN_US,
        name_zh_hk: category.NAME_ZH_HK,
        name_zh_cn: category.NAME_ZH_CN,
        sorting: category.SORTING,
        show_home: category.SHOW_HOME,
        product_count: category.PRODUCT_COUNT,
        enabled: category.ENABLED,
        theme: category.THEME,
        children: []
      };
      var all = {
        id: category.ID,
        type: category.TYPE,
        parent_id: category.PARENT_ID,
        name: 'All',
        name_en_us: 'All',
        name_zh_hk: '全部',
        name_zh_cn: '全部',
        sorting: category.SORTING,
        show_home: category.SHOW_HOME,
        product_count: category.PRODUCT_COUNT,
        enabled: category.ENABLED,
        theme: category.THEME,
        children: []
      };
      data.children.push(all);
      this.sqlite.create({
        name: this.activate.prefix + this.activate.path + '.db',
        location: 'default'
      }).then((db: SQLiteObject) => {
        db.transaction((tx) => {
          tx.executeSql('SELECT * FROM "PRODUCT_CATEGORY" WHERE "ENABLED" = 1 AND "PARENT_ID" = ? ORDER BY "SORTING" DESC', [category.ID],
            (tx, res) => {
              for (var i = 0; i < res.rows.length; i++) {
                data.children.push(this.getCategoryNestedData(res.rows.item(i), locale));
              }
            },
            (tx, err) => {
              console.log('db execute fail' + err);
              reject(err);
            });
        });
      }, (err) => {
        console.log('sqlite create fail' + err);
        reject(err);
      });
    });

  }

  /**************************************************
  // offline: getCategory
  **************************************************/
  getCategory(params) {
    return new Promise((resolve, reject) => {
      this.sqlite.create({
        name: this.activate.prefix + this.activate.path + '.db',
        location: 'default'
      }).then((db: SQLiteObject) => {
        db.transaction((tx) => {
          tx.executeSql('SELECT * FROM "PRODUCT_CATEGORY" WHERE "ENABLED" = 1 AND "PARENT_ID" = 0 ORDER BY "SORTING" DESC', [],
            (tx, res) => {
              let ret = {
                status: 'Y',
                msg: '',
                data: []
              };
              // TODO: getCategoryNestedData
              if (res.rows.length > 0) {
                for (let i = 0; i < res.rows.length; i++) {
                  ret.data.push(this.getCategoryNestedData(res.rows.item(i), params.locale));
                }
              }
              console.log(ret);
              console.log('getCategory completed');
              resolve(ret);
            },
            (tx, err) => {
              console.log('db execute fail' + err);
              reject(err);
            });
        });
      }, (err) => {
        console.log('sqlite create fail' + err);
        reject(err);
      });
    });
  }

  /**************************************************
  // offline: getInvoiceList
  **************************************************/
  getInvoiceList(params) {
    return new Promise((resolve, reject) => {
      this.sqlite.create({
        name: this.activate.prefix + this.activate.path + '.db',
        location: 'default'
      }).then((db: SQLiteObject) => {

        //prepare sql
        var status_sql = "";
        if (typeof params.status != "undefined" && params.status != []) {
          status_sql = status_sql + " where (";
          for (var ii = 0; ii < params.status.length; ii++) {
            if (ii != 0) {
              status_sql = status_sql + " OR ";
            }
            status_sql = status_sql + "INVOICE.STATUS = " + params.status[ii];

          }
          status_sql = status_sql + ")";

        } else {
          status_sql = " where INVOICE.STATUS = 0";
        }
        if (typeof params.keyword != "undefined" && params.keyword != null) {
          status_sql = status_sql + " AND (";
          status_sql = status_sql + "INVOICE.INVOICE_NO LIKE '%" + params.keyword + "%' OR ";
          status_sql = status_sql + "INVOICE.BILLING_LAST_NAME LIKE '%" + params.keyword + "%' OR ";
          status_sql = status_sql + "INVOICE.BILLING_FIRST_NAME LIKE '%" + params.keyword + "%')";

        }
        status_sql = status_sql + " and NOT(INVOICE.FB_TABLE = 0 AND IITEM.ITEM_TOTAL = 0) order by INVOICE.ID desc";

        var limit_from = 0;
        var limit = 20;
        if (typeof params.limit_from != "undefined" && params.limit_from != null) {
          limit_from = params.limit_from;
        }
        if (typeof params.limit != "undefined" && params.limit != null) {
          limit = params.limit;
        }
        var run_sql = 'SELECT * from INVOICE left join (select INVOICE_ID, count(*) as ITEM_COUNT , sum(UNIT_PRICE*QTY) as ITEM_TOTAL, count(case when SERVED = 1 AND QTY > 0 then 1 else null end) as SERVED_QTY, count(case when QTY > 0 then 1 else null end) as CC_QTY from INVOICE_ITEM group by INVOICE_ID) as IITEM on IITEM.INVOICE_ID = INVOICE.ID' + status_sql;
        db.transaction((tx) => {
          tx.executeSql(run_sql, [],
            (tx, res) => {
              let ret = {
                status: 'Y',
                msg: '',
                data: {
                  count: res.rows.length,
                  list: []
                }
              };
              var invoice_charge_index = 0;
              for (var i = limit_from; i < limit + limit_from && i < res.rows.length; i++) {
                var record = res.rows.item(i);
                this.checkUndefined(record);
                var serve_status = this.emptyTozero(record.SERVED_QTY) + '/' + this.emptyTozero(record.CC_QTY);
                var inv_info = {
                  id: record.ID,
                  table_num: this.emptyTozero(record.FB_TABLE),
                  invoice_no: record.INVOICE_NO,
                  item_count: this.emptyTozero(record.ITEM_COUNT),
                  currency: record.CURRENCY,
                  gender: this.emptyTozero(record.GENDER),
                  billing_last_name: record.BILLING_LAST_NAME,
                  price: this.emptyTozero(record.ITEM_TOTAL),
                  status_num: this.emptyTozero(record.STATUS),
                  invoice_date: record.INVOICE_DATE,
                  bill_to: record.BILLING_FIRST_NAME + ' ' + record.BILLING_LAST_NAME,
                  member_mobile: record.BILLING_COUNTRY_CODE + ' ' + record.BILLING_MOBILE,
                  delivery_type: record.DELIVERY_TYPE,
                  check_out_warehouse: this.emptyTozero(record.CHECK_OUT_WAREHOUSE_ID),
                  pick_up_warehouse_id: this.emptyTozero(record.PICK_UP_WAREHOUSE_ID),
                  expected_delivery_date: record.EXPECTED_DELIVERY_DATE,
                  name: 'Offline Visitor',
                  user_id: this.emptyTozero(record.USER_ID),
                  member_id: 0,
                  vip_level: 0,
                  member_profile: [],
                  served_qty: serve_status,
                  sync: this.emptyTozero(record.CARRY_UP),
                };

                ret.data.list.push(inv_info);

                if (inv_info.bill_to != ' ') {
                  inv_info.name = inv_info.bill_to;
                }

                var iid = record.ID;
                var run_sql = 'SELECT * from INVOICE_CHARGES where INVOICE_ID=?';
                tx.executeSql(run_sql, [iid],
                  (tx, inv_charge) => {
                    var baseDiscount = 0;
                    var baseCharge = 0;
                    var baseTotal = ret.data.list[invoice_charge_index].price;

                    for (var j = 0; j < inv_charge.rows.length; j++) {
                      var invoice_charge = inv_charge.rows.item(j);
                      this.checkUndefined(invoice_charge);
                      if (invoice_charge.VALUE_TYPE == 'percent') {
                        if (invoice_charge.SIGN == '+') {
                          var totalFlag = baseTotal;
                          baseTotal *= 1 + Number(invoice_charge.VALUE) / 100;
                          baseCharge += baseTotal - totalFlag;
                        } else {
                          var totalFlag = baseTotal;
                          baseTotal *= 1 - Number(invoice_charge.VALUE) / 100;
                          baseDiscount += totalFlag - baseTotal;
                        }
                      }

                    }

                    for (var j = 0; j < inv_charge.rows.length; j++) {
                      var invoice_charge = inv_charge.rows.item(j);
                      this.checkUndefined(invoice_charge);
                      if (invoice_charge.VALUE_TYPE != 'percent') {
                        if (invoice_charge.SIGN == '+') {
                          var totalFlag = baseTotal;
                          baseTotal += Number(invoice_charge.VALUE);
                          baseCharge += baseTotal - totalFlag;
                        } else {
                          var totalFlag = baseTotal;
                          baseTotal -= Number(invoice_charge.VALUE);
                          baseDiscount += totalFlag - baseTotal;
                        }
                      }

                    }

                    ret.data.list[invoice_charge_index++].price = baseTotal;

                    if (limit == ret.data.list.length || res.rows.length == ret.data.list.length) {

                      resolve(ret);
                    }

                  },
                  function (err) {
                    reject(err);
                  });

              }
              if (0 == ret.data.list.length) {
                //console.log(ret);
                resolve(ret);
              }
              console.log('getInvoiceList completed');
              resolve(ret);
            },
            (tx, err) => {
              console.log('db execute fail' + err);
              reject(err);
            });
        });
      }, (err) => {
        console.log('sqlite create fail' + err);
        reject(err);
      });
    });
  }

  /**************************************************
  // offline: getInvoiceDetail
  **************************************************/
  getInvoiceDetail(params) {
    return new Promise((resolve, reject) => {
      this.sqlite.create({
        name: this.activate.prefix + this.activate.path + '.db',
        location: 'default'
      }).then((db: SQLiteObject) => {


        if (typeof params.fb_table != "undefined" && params.fb_table != null) {
          db.transaction((tx) => {
            // update table num
            var run_sql = 'UPDATE "INVOICE" set "FB_TABLE" = ? where ID = ?';
            tx.executeSql(run_sql, [params.fb_table, params.invoice_id],
              (tx, update_res) => {
                //run get invoice detail sql
                run_sql = 'SELECT * from INVOICE left join (select INVOICE_ID, count(*) as ITEM_COUNT , sum(UNIT_PRICE*QTY) as ITEM_TOTAL  from INVOICE_ITEM group by INVOICE_ID) as IITEM on IITEM.INVOICE_ID = INVOICE.ID where INVOICE.ID = ?';
                tx.executeSql(run_sql, [params.invoice_id],
                  (tx, res) => {
                    var record = res.rows.item(0);
                    this.checkUndefined(record);
                    var ret = {
                      status: 'Y',
                      msg: '',
                      data: {
                        id: record.ID,
                        table_num: record.FB_TABLE,
                        invoice_no: record.INVOICE_NO,
                        ticket_num: record.INVOICE_NO.slice(-4),
                        currency: record.CURRENCY,
                        gender: record.GENDER,
                        billing_area_id: this.emptyTozero(record.BILLING_AREA_ID),
                        billing_region_id: this.emptyTozero(record.BILLING_REGION_ID),
                        billing_district_id: this.emptyTozero(record.BILLING_DISTRICT_ID),
                        billing_country_id: this.emptyTozero(record.BILLING_COUNTRY_ID),
                        billing_address_1: record.BILLING_ADDRESS_1,
                        billing_address_2: record.BILLING_ADDRESS_2,
                        billing_address_3: record.BILLING_ADDRESS_3,
                        billing_address_4: record.BILLING_ADDRESS_4,
                        billing_first_name: record.BILLING_FIRST_NAME,
                        billing_last_name: record.BILLING_LAST_NAME,
                        billing_contact: record.BILLING_FIRST_NAME + ' ' + record.BILLING_LAST_NAME,
                        billing_email: record.BILLING_EMAIL,
                        billing_mobile: record.BILLING_COUNTRY_CODE + ' ' + record.BILLING_MOBILE,

                        pick_up_contact: record.BILLING_FIRST_NAME + ' ' + record.BILLING_LAST_NAME,
                        pick_up_first_name: record.BILLING_FIRST_NAME,
                        pick_up_last_name: record.BILLING_LAST_NAME,
                        pick_up_country_code: record.BILLING_COUNTRY_CODE,
                        pick_up_mobile: record.BILLING_MOBILE,
                        pick_up_email: record.BILLING_EMAIL,

                        shipping_area_id: this.emptyTozero(record.SHIPPING_AREA_ID),
                        shipping_region_id: this.emptyTozero(record.SHIPPING_REGION_ID),
                        shipping_district_id: this.emptyTozero(record.SHIPPING_DISTRICT_ID),
                        shipping_country_id: this.emptyTozero(record.SHIPPING_COUNTRY_ID),
                        shipping_address_1: record.BILLING_ADDRESS_1,
                        shipping_address_2: record.BILLING_ADDRESS_2,
                        shipping_address_3: record.BILLING_ADDRESS_3,
                        shipping_address_4: record.BILLING_ADDRESS_4,
                        shipping_first_name: record.SHIPPING_FIRST_NAME,
                        shipping_last_name: record.SHIPPING_LAST_NAME,
                        shipping_contact: record.SHIPPING_FIRST_NAME + ' ' + record.SHIPPING_LAST_NAME,
                        shipping_email: record.SHIPPING_EMAIL,
                        shipping_mobile: record.SHIPPING_COUNTRY_CODE + ' ' + record.SHIPPING_MOBILE,

                        item_count: record.ITEM_COUNT,
                        item_total: this.emptyTozero(record.ITEM_TOTAL),
                        pay_method: record.PAY_METHOD,
                        status_num: this.emptyTozero(record.STATUS),

                        deposit: this.emptyTozero(record.PAYED_AMOUNT),
                        payment_type: record.PAYMENT_TYPE,
                        payed_amount: this.emptyTozero(record.PAYED_AMOUNT),

                        delivery_type: record.DELIVERY_TYPE,
                        remark: record.REMARK,
                        products: [],
                        department_list: [],

                        invoice_date: record.INVOICE_DATE,
                        check_out_warehouse: this.emptyTozero(record.CHECK_OUT_WAREHOUSE_ID),
                        pick_up_warehouse_id: this.emptyTozero(record.PICK_UP_WAREHOUSE_ID),
                        pick_up_site: record.PICK_UP_SITE,
                        expected_delivery_date: record.EXPECTED_DELIVERY_DATE,
                        invoice_charges: [],

                        delivery_total: this.emptyTozero(record.DELIVERY_TOTAL),
                        refund_total: this.emptyTozero(record.REFUND_TOTAL),
                        refund: this.emptyTozero(record.REFUND_TOTAL),
                        grand_total: this.emptyTozero(record.ITEM_TOTAL),
                        service_total: 0,
                        discount_total: 0,

                        octopus_info: JSON.parse(record.OCTOPUS_INFO),

                        customer_name: 'Visitor',
                        customer_info: {
                          account_no: 'visitor',
                          user_id: 0,
                          customer_name: '',
                          customer_first_name: '',
                          customer_last_name: '',
                          customer_email: '',
                          customer_country_code: '',
                          customer_mobile: '',
                          vip_level: ''
                        }
                      }
                    };
                    //get invoice item
                    run_sql = 'SELECT *,INVOICE_ITEM.ID as ITEM_ID, INVOICE_ITEM.ADDONS as ITEM_ADDONS from INVOICE_ITEM left join (select PRODUCT.DEPARTMENT_ID, PRODUCT.ID, PRODUCT.NAME_EN_US, PRODUCT.NAME_ZH_HK, PRODUCT.NAME_ZH_CN, PRODUCT.HIGHLIGHT_EN_US,PRODUCT.HIGHLIGHT_ZH_HK, PRODUCT.HIGHLIGHT_ZH_CN, PRODUCT.CODE, PRODUCT.WEIGHT, PRODUCT.VOLUME, PRODUCT.REMARK, PRODUCT.SPEC_FOR_OFFLINE, PM.ID as PHOTO_ID from PRODUCT LEFT JOIN (SELECT MIN(ID) as ID,PRODUCT_ID FROM PRODUCT_MEDIA group by PRODUCT_ID) as PM on PRODUCT.ID = PM.PRODUCT_ID) as FP on INVOICE_ITEM.PRODUCT_ID = FP.ID where INVOICE_ID=?';
                    tx.executeSql(run_sql, [record.ID],
                      (tx, inv_item) => {

                        ret.data.item_count = inv_item.rows.length;

                        for (var i = 0; i < inv_item.rows.length; i++) {

                          var invoice_item = inv_item.rows.item(i);
                          this.checkUndefined(invoice_item);
                          var photo_url = this.helperService.getRootPath + this.secondPath + invoice_item.PHOTO_ID;

                          var product_info = {
                            id: invoice_item.PRODUCT_ID,
                            item_id: invoice_item.ITEM_ID,
                            name: invoice_item['NAME_' + params.locale],
                            highlight: invoice_item['HIGHLIGHT_' + params.locale],
                            photo: photo_url,
                            qty: this.emptyTozero(invoice_item.QTY),
                            buy_qty: this.emptyTozero(invoice_item.QTY),
                            sku_no: invoice_item.SKU_NO,
                            code: invoice_item.CODE,
                            options: [],
                            currency: invoice_item.CURRENCY,
                            o_price: this.emptyTozero(invoice_item.UNIT_PRICE),
                            unit_price: this.emptyTozero(invoice_item.UNIT_PRICE),
                            actual_unit_price: this.emptyTozero(invoice_item.UNIT_PRICE),
                            sub_total: this.emptyTozero(invoice_item.UNIT_PRICE * invoice_item.QTY),
                            //buy_sub_total
                            //actual sub total
                            weight: invoice_item.WEIGHT,
                            volumn: invoice_item.VOLUMN,
                            remark: invoice_item.REMARK,
                            served: invoice_item.SERVED,
                            specifications: JSON.parse(invoice_item.SPEC_FOR_OFFLINE),
                            addons: JSON.parse(invoice_item.ITEM_ADDONS),
                            department_id: invoice_item.DEPARTMENT_ID,
                            department_name: '',
                            department_name_EN_US: '',
                            department_name_ZH_HK: '',
                            department_name_ZH_CN: '',
                          };

                          var spec_array = JSON.parse(invoice_item.SPEC_FOR_OFFLINE);

                          var oids = [];

                          var attribute = invoice_item.SKU_NO.split("-");
                          var len = attribute.length;

                          for (var j = 0; j < len - 1; j++) {
                            var tokens = attribute[j].split("Y");
                            var dict_id = parseInt(tokens[0].split("X")[1], 16);
                            var opt_id = parseInt(tokens[1], 16);
                            oids.push(opt_id);
                          }

                          for (var sa = 0; sa < spec_array.length; sa++) {
                            var spec = spec_array[sa];
                            var dict_opt = {
                              title: spec.title,
                              options: []
                            };


                            for (var oi = 0; oi < spec.options.length; oi++) {

                              if (oids.indexOf(spec.options[oi]['id']) != -1) {

                                dict_opt.options.push(spec.options[oi]['name']);
                              }
                            }

                            product_info.options.push(dict_opt);

                          }

                          ret.data.products.push(product_info);


                        }

                        var baseDiscount = 0;
                        var baseCharge = 0;
                        var baseTotal = record.ITEM_TOTAL;

                        run_sql = 'SELECT * from INVOICE_CHARGES where INVOICE_ID=?';

                        tx.executeSql(run_sql, [record.ID],
                          (tx, inv_charge) => {

                            for (var i = 0; i < inv_charge.rows.length; i++) {
                              var invoice_charge = inv_charge.rows.item(i);
                              this.checkUndefined(invoice_charge);

                              var charge_info = {
                                title_EN_US: invoice_charge.NAME_EN_US,
                                title_ZH_HK: invoice_charge.NAME_ZH_HK,
                                title_ZH_CN: invoice_charge.NAME_ZH_CN,
                                sign: invoice_charge.SIGN,
                                type: invoice_charge.TYPE,
                                value_type: invoice_charge.VALUE_TYPE,
                                value: invoice_charge.VALUE,

                              };
                              ret.data.invoice_charges.push(charge_info);

                            }

                            for (var i = 0; i < inv_charge.rows.length; i++) {
                              var invoice_charge = inv_charge.rows.item(i);
                              this.checkUndefined(invoice_charge);
                              if (invoice_charge.VALUE_TYPE == 'percent') {
                                if (invoice_charge.SIGN == '+') {
                                  var totalFlag = baseTotal;
                                  baseTotal *= 1 + Number(invoice_charge.VALUE) / 100;
                                  baseCharge += baseTotal - totalFlag;
                                } else {
                                  var totalFlag = baseTotal;
                                  baseTotal *= 1 - Number(invoice_charge.VALUE) / 100;
                                  baseDiscount += totalFlag - baseTotal;
                                }
                              }

                            }

                            for (var i = 0; i < inv_charge.rows.length; i++) {
                              var invoice_charge = inv_charge.rows.item(i);
                              this.checkUndefined(invoice_charge);
                              if (invoice_charge.VALUE_TYPE != 'percent') {
                                if (invoice_charge.SIGN == '+') {
                                  var totalFlag = baseTotal;
                                  baseTotal += Number(invoice_charge.VALUE);
                                  baseCharge += baseTotal - totalFlag;
                                } else {
                                  var totalFlag = baseTotal;
                                  baseTotal -= Number(invoice_charge.VALUE);
                                  baseDiscount += totalFlag - baseTotal;
                                }
                              }

                            }

                            ret.data.grand_total = baseTotal;
                            ret.data.discount_total = 0;
                            ret.data.service_total = 0;

                            // get department
                            var cur_products = ret.data.products;
                            var pro_count = 0;

                            if (cur_products.length != 0) {
                              for (var pro_dep = 0; pro_dep < cur_products.length; pro_dep++) {
                                var product = cur_products[pro_dep];
                                run_sql = 'SELECT * from `OPTION` where ID=?';
                                tx.executeSql(run_sql, [product.department_id],
                                  (tx, opt_record) => {
                                    var option = opt_record.rows.item(0);
                                    this.checkUndefined(option);

                                    ret.data.products[pro_count].department_name = option['NAME_' + params.locale];
                                    ret.data.products[pro_count].department_name_EN_US = option.NAME_EN_US;
                                    ret.data.products[pro_count].department_name_ZH_HK = option.NAME_ZH_HK;
                                    ret.data.products[pro_count].department_name_ZH_CN = option.NAME_ZH_CN;
                                    if (ret.data.department_list.indexOf(option.ID) == -1) {
                                      ret.data.department_list.push(option.ID);
                                    }
                                    pro_count++;
                                    if (pro_count == cur_products.length) {
                                      resolve(ret);
                                    }

                                  },
                                  (tx, err) => {
                                    reject(err);
                                  });
                              }
                            }
                            else {
                              resolve(ret);
                            }



                          },
                          (tx, err) => {
                            reject(err);
                          });





                      },
                      (tx, err) => {
                        reject(err);
                      });




                  },
                  (tx, err) => {
                    reject(err);
                  });

              },
              (tx, err) => {
                reject(err);
              });



          });


        }
        else {
          db.transaction((tx) => {
            //run get invoice detail sql
            var run_sql = 'SELECT * from INVOICE left join (select INVOICE_ID, count(*) as ITEM_COUNT , sum(UNIT_PRICE*QTY) as ITEM_TOTAL  from INVOICE_ITEM group by INVOICE_ID) as IITEM on IITEM.INVOICE_ID = INVOICE.ID where INVOICE.ID = ?';
            tx.executeSql(run_sql, [params.invoice_id],
              (tx, res) => {
                var record = res.rows.item(0);
                this.checkUndefined(record);
                var ret = {
                  status: 'Y',
                  msg: '',
                  data: {
                    id: record.ID,
                    table_num: record.FB_TABLE,
                    invoice_no: record.INVOICE_NO,
                    ticket_num: record.INVOICE_NO.slice(-4),
                    currency: record.CURRENCY,
                    gender: record.GENDER,
                    billing_area_id: this.emptyTozero(record.BILLING_AREA_ID),
                    billing_region_id: this.emptyTozero(record.BILLING_REGION_ID),
                    billing_district_id: this.emptyTozero(record.BILLING_DISTRICT_ID),
                    billing_country_id: this.emptyTozero(record.BILLING_COUNTRY_ID),
                    billing_address_1: record.BILLING_ADDRESS_1,
                    billing_address_2: record.BILLING_ADDRESS_2,
                    billing_address_3: record.BILLING_ADDRESS_3,
                    billing_address_4: record.BILLING_ADDRESS_4,
                    billing_first_name: record.BILLING_FIRST_NAME,
                    billing_last_name: record.BILLING_LAST_NAME,
                    billing_contact: record.BILLING_FIRST_NAME + ' ' + record.BILLING_LAST_NAME,
                    billing_email: record.BILLING_EMAIL,
                    billing_mobile: record.BILLING_COUNTRY_CODE + ' ' + record.BILLING_MOBILE,

                    pick_up_contact: record.BILLING_FIRST_NAME + ' ' + record.BILLING_LAST_NAME,
                    pick_up_first_name: record.BILLING_FIRST_NAME,
                    pick_up_last_name: record.BILLING_LAST_NAME,
                    pick_up_country_code: record.BILLING_COUNTRY_CODE,
                    pick_up_mobile: record.BILLING_MOBILE,
                    pick_up_email: record.BILLING_EMAIL,

                    shipping_area_id: this.emptyTozero(record.SHIPPING_AREA_ID),
                    shipping_region_id: this.emptyTozero(record.SHIPPING_REGION_ID),
                    shipping_district_id: this.emptyTozero(record.SHIPPING_DISTRICT_ID),
                    shipping_country_id: this.emptyTozero(record.SHIPPING_COUNTRY_ID),
                    shipping_address_1: record.BILLING_ADDRESS_1,
                    shipping_address_2: record.BILLING_ADDRESS_2,
                    shipping_address_3: record.BILLING_ADDRESS_3,
                    shipping_address_4: record.BILLING_ADDRESS_4,
                    shipping_first_name: record.SHIPPING_FIRST_NAME,
                    shipping_last_name: record.SHIPPING_LAST_NAME,
                    shipping_contact: record.SHIPPING_FIRST_NAME + ' ' + record.SHIPPING_LAST_NAME,
                    shipping_email: record.SHIPPING_EMAIL,
                    shipping_mobile: record.SHIPPING_COUNTRY_CODE + ' ' + record.SHIPPING_MOBILE,

                    item_count: record.ITEM_COUNT,
                    item_total: this.emptyTozero(record.ITEM_TOTAL),
                    pay_method: record.PAY_METHOD,
                    status_num: this.emptyTozero(record.STATUS),

                    deposit: this.emptyTozero(record.PAYED_AMOUNT),
                    payment_type: record.PAYMENT_TYPE,
                    payed_amount: this.emptyTozero(record.PAYED_AMOUNT),

                    delivery_type: record.DELIVERY_TYPE,
                    remark: record.REMARK,
                    products: [],
                    department_list: [],

                    invoice_date: record.INVOICE_DATE,
                    check_out_warehouse: this.emptyTozero(record.CHECK_OUT_WAREHOUSE_ID),
                    pick_up_warehouse_id: this.emptyTozero(record.PICK_UP_WAREHOUSE_ID),
                    pick_up_site: record.PICK_UP_SITE,
                    expected_delivery_date: record.EXPECTED_DELIVERY_DATE,
                    invoice_charges: [],

                    delivery_total: this.emptyTozero(record.DELIVERY_TOTAL),
                    refund_total: this.emptyTozero(record.REFUND_TOTAL),
                    refund: this.emptyTozero(record.REFUND_TOTAL),
                    grand_total: this.emptyTozero(record.ITEM_TOTAL),
                    discount_total: 0,
                    service_total: 0,

                    octopus_info: JSON.parse(record.OCTOPUS_INFO),

                    customer_name: 'Visitor',
                    customer_info: {
                      account_no: 'visitor',
                      user_id: 0,
                      customer_name: '',
                      customer_first_name: '',
                      customer_last_name: '',
                      customer_email: '',
                      customer_country_code: '',
                      customer_mobile: '',
                      vip_level: ''
                    }
                  }
                };
                //get invoice item
                run_sql = 'SELECT *,INVOICE_ITEM.ID as ITEM_ID, INVOICE_ITEM.ADDONS as ITEM_ADDONS from INVOICE_ITEM left join (select PRODUCT.DEPARTMENT_ID, PRODUCT.ID, PRODUCT.NAME_EN_US, PRODUCT.NAME_ZH_HK, PRODUCT.NAME_ZH_CN, PRODUCT.HIGHLIGHT_EN_US,PRODUCT.HIGHLIGHT_ZH_HK, PRODUCT.HIGHLIGHT_ZH_CN, PRODUCT.CODE, PRODUCT.WEIGHT, PRODUCT.VOLUME, PRODUCT.REMARK, PRODUCT.SPEC_FOR_OFFLINE, PM.ID as PHOTO_ID from PRODUCT LEFT JOIN (SELECT MIN(ID) as ID,PRODUCT_ID FROM PRODUCT_MEDIA group by PRODUCT_ID) as PM on PRODUCT.ID = PM.PRODUCT_ID) as FP on INVOICE_ITEM.PRODUCT_ID = FP.ID where INVOICE_ID=?';
                tx.executeSql(run_sql, [record.ID],
                  (tx, inv_item) => {

                    ret.data.item_count = inv_item.rows.length;

                    for (var i = 0; i < inv_item.rows.length; i++) {

                      var invoice_item = inv_item.rows.item(i);
                      this.checkUndefined(invoice_item);
                      var photo_url = this.helperService.getRootPath + this.secondPath + invoice_item.PHOTO_ID;

                      var product_info = {
                        id: invoice_item.PRODUCT_ID,
                        item_id: invoice_item.ITEM_ID,
                        name: invoice_item['NAME_' + params.locale],
                        highlight: invoice_item['HIGHLIGHT_' + params.locale],
                        photo: photo_url,
                        qty: this.emptyTozero(invoice_item.QTY),
                        buy_qty: this.emptyTozero(invoice_item.QTY),
                        sku_no: invoice_item.SKU_NO,
                        code: invoice_item.CODE,
                        options: [],
                        currency: invoice_item.CURRENCY,
                        o_price: this.emptyTozero(invoice_item.UNIT_PRICE),
                        unit_price: this.emptyTozero(invoice_item.UNIT_PRICE),
                        actual_unit_price: this.emptyTozero(invoice_item.UNIT_PRICE),
                        sub_total: this.emptyTozero(invoice_item.UNIT_PRICE * invoice_item.QTY),
                        //buy_sub_total
                        //actual sub total
                        weight: invoice_item.WEIGHT,
                        volumn: invoice_item.VOLUMN,
                        remark: invoice_item.REMARK,
                        served: invoice_item.SERVED,
                        specifications: JSON.parse(invoice_item.SPEC_FOR_OFFLINE),
                        addons: JSON.parse(invoice_item.ITEM_ADDONS),
                        department_id: invoice_item.DEPARTMENT_ID,
                        department_name: '',
                        department_name_EN_US: '',
                        department_name_ZH_HK: '',
                        department_name_ZH_CN: '',
                      };

                      var spec_array = JSON.parse(invoice_item.SPEC_FOR_OFFLINE);

                      var oids = [];

                      var attribute = invoice_item.SKU_NO.split("-");
                      var len = attribute.length;

                      for (var j = 0; j < len - 1; j++) {
                        var tokens = attribute[j].split("Y");
                        var dict_id = parseInt(tokens[0].split("X")[1], 16);
                        var opt_id = parseInt(tokens[1], 16);
                        oids.push(opt_id);
                      }

                      for (var sa = 0; sa < spec_array.length; sa++) {
                        var spec = spec_array[sa];
                        var dict_opt = {
                          title: spec.title,
                          options: []
                        };


                        for (var oi = 0; oi < spec.options.length; oi++) {

                          if (oids.indexOf(spec.options[oi]['id']) != -1) {

                            dict_opt.options.push(spec.options[oi]['name']);
                          }
                        }

                        product_info.options.push(dict_opt);

                      }

                      ret.data.products.push(product_info);


                    }

                    var baseDiscount = 0;
                    var baseCharge = 0;
                    var baseTotal = record.ITEM_TOTAL;

                    run_sql = 'SELECT * from INVOICE_CHARGES where INVOICE_ID=?';

                    tx.executeSql(run_sql, [record.ID],
                      (tx, inv_charge) => {

                        for (var i = 0; i < inv_charge.rows.length; i++) {
                          var invoice_charge = inv_charge.rows.item(i);
                          this.checkUndefined(invoice_charge);

                          var charge_info = {
                            title_EN_US: invoice_charge.NAME_EN_US,
                            title_ZH_HK: invoice_charge.NAME_ZH_HK,
                            title_ZH_CN: invoice_charge.NAME_ZH_CN,
                            sign: invoice_charge.SIGN,
                            type: invoice_charge.TYPE,
                            value_type: invoice_charge.VALUE_TYPE,
                            value: invoice_charge.VALUE,

                          };
                          ret.data.invoice_charges.push(charge_info);

                        }

                        for (var i = 0; i < inv_charge.rows.length; i++) {
                          var invoice_charge = inv_charge.rows.item(i);
                          this.checkUndefined(invoice_charge);
                          if (invoice_charge.VALUE_TYPE == 'percent') {
                            if (invoice_charge.SIGN == '+') {
                              var totalFlag = baseTotal;
                              baseTotal *= 1 + Number(invoice_charge.VALUE) / 100;
                              baseCharge += baseTotal - totalFlag;
                            } else {
                              var totalFlag = baseTotal;
                              baseTotal *= 1 - Number(invoice_charge.VALUE) / 100;
                              baseDiscount += totalFlag - baseTotal;
                            }
                          }

                        }

                        for (var i = 0; i < inv_charge.rows.length; i++) {
                          var invoice_charge = inv_charge.rows.item(i);
                          this.checkUndefined(invoice_charge);
                          if (invoice_charge.VALUE_TYPE != 'percent') {
                            if (invoice_charge.SIGN == '+') {
                              var totalFlag = baseTotal;
                              baseTotal += Number(invoice_charge.VALUE);
                              baseCharge += baseTotal - totalFlag;
                            } else {
                              var totalFlag = baseTotal;
                              baseTotal -= Number(invoice_charge.VALUE);
                              baseDiscount += totalFlag - baseTotal;
                            }
                          }

                        }

                        ret.data.grand_total = baseTotal;

                        // get department
                        var cur_products = ret.data.products;
                        var pro_count = 0;

                        if (cur_products.length != 0) {
                          for (var pro_dep = 0; pro_dep < cur_products.length; pro_dep++) {
                            var product = cur_products[pro_dep];
                            run_sql = 'SELECT * from `OPTION` where ID=?';
                            tx.executeSql(run_sql, [product.department_id],
                              (tx, opt_record) => {
                                var option = opt_record.rows.item(0);
                                this.checkUndefined(option);

                                ret.data.products[pro_count].department_name = option['NAME_' + params.locale];
                                ret.data.products[pro_count].department_name_EN_US = option.NAME_EN_US;
                                ret.data.products[pro_count].department_name_ZH_HK = option.NAME_ZH_HK;
                                ret.data.products[pro_count].department_name_ZH_CN = option.NAME_ZH_CN;
                                if (ret.data.department_list.indexOf(option.ID) == -1) {
                                  ret.data.department_list.push(option.ID);
                                }
                                pro_count++;
                                if (pro_count == cur_products.length) {
                                  resolve(ret);
                                }

                              },
                              (tx, err) => {
                                reject(err);
                              });
                          }
                        }
                        else {
                          resolve(ret);
                        }



                      },
                      (tx, err) => {
                        reject(err);
                      });
                  },
                  (tx, err) => {
                    reject(err);
                  });
              },
              (tx, err) => {
                reject(err);
              });
          });
        }

      }, (err) => {
        console.log('sqlite create fail' + err);
        reject(err);
      });
    });
  }


  /**************************************************
  // offline: getProductList
  **************************************************/
  getProductList(params) {
    return new Promise((resolve, reject) => {
      this.sqlite.create({
        name: this.activate.prefix + this.activate.path + '.db',
        location: 'default'
      }).then((db: SQLiteObject) => {
        db.transaction((tx) => {


          var cat_param = "";
          if (params.category_ids && Array.isArray(params.category_ids)) {

            cat_param = cat_param + " AND (";
            for (var n = 0; n < params.category_ids.length; n++) {
              if (n != 0) {
                cat_param = cat_param + ' OR '
              }
              cat_param = cat_param + "CATEGORY_IDS LIKE '%," + params.category_ids[n] + ",%'";

            }
            cat_param = cat_param + ')';
          }
          var search = "";
          //console.log(params.keyword);
          if (typeof params.keyword != "undefined" && params.keyword != null) {
            search = search + " AND (";
            search = search + "NAME_EN_US LIKE '%" + params.keyword + "%' OR ";
            search = search + "NAME_ZH_HK LIKE '%" + params.keyword + "%' OR ";
            search = search + "NAME_ZH_CN LIKE '%" + params.keyword + "%')";

          }
          var limit_from = 0;
          var limit = 20;
          if (typeof params.limit_from != "undefined" && params.limit_from != null) {
            limit_from = params.limit_from;
          }
          if (typeof params.limit != "undefined" && params.limit != null) {
            limit = params.limit;
          }

          var run_sql = 'SELECT PRODUCT.*, PRODUCT_MEDIA.ID AS MEDIA_ID FROM PRODUCT LEFT OUTER JOIN PRODUCT_MEDIA ON PRODUCT.ID = PRODUCT_MEDIA.PRODUCT_ID WHERE PRODUCT.ENABLE_FOR_POS = 1 ' + cat_param + search + ' GROUP BY PRODUCT.ID ORDER BY PRODUCT.SORTING DESC, PRODUCT.ID DESC';

          tx.executeSql(run_sql, [],
            (tx, res) => {
              let ret = {
                status: 'Y',
                msg: '',
                data: {
                  advertisements: {
                    count: 0,
                    list: []
                  },
                  products: {
                    count: res.rows.length,
                    list: []
                  }

                }
              };

              for (var i = limit_from; i < limit + limit_from && i < res.rows.length; i++) {

                var record = res.rows.item(i);
                var photo_url = this.helperService.getRootPath + this.secondPath + record.MEDIA_ID;

                this.checkUndefined(record);
                var product_info = {
                  id: record.ID,
                  code: record.CODE,
                  gift: record.GIFT,
                  category_ids: record.CATEGORY_IDS.substring(1, record.CATEGORY_IDS.length - 1).split(","),
                  name: record['NAME_' + params.locale],
                  highlight: record['HIGHLIGHT_' + params.locale],
                  photo: photo_url,
                  currency: record.CURRENCY,
                  price: record.OFF_PRICE,
                  //specifications : offline.getProductSpec(record.ID),
                  //original_price : offline.getProductPrice(record.ID, record.CURRENCY),
                  qty: record.QTY,
                  presale_qty: record.PRESALE_QTY,
                  sorting: record.SORTING,
                  enabled: record.ENABLED,
                  enable_for_pos: record.ENABLE_FOR_POS,
                  show_price: record.SHOP_PRICE,
                  specifications: JSON.parse(record.SPEC_FOR_OFFLINE),
                  addons: JSON.parse(record.ADDONS),
                };

                ret.data.products.list.push(product_info);

              }
              console.log('getProductList completed');
              resolve(ret);

            },
            (tx, err) => {
              console.log('db execute fail' + err);
              reject(err);
            });
        });
      }, (err) => {
        console.log('sqlite create fail' + err);
        reject(err);
      });
    });
  }


  /**************************************************
  // offline: getProductDetail
  **************************************************/
  getProductDetail(params) {
    return new Promise((resolve, reject) => {
      this.sqlite.create({
        name: this.activate.prefix + this.activate.path + '.db',
        location: 'default'
      }).then((db: SQLiteObject) => {
        db.transaction((tx) => {

          var run_sql = 'SELECT * from PRODUCT LEFT JOIN (SELECT ID, PRODUCT_ID FROM PRODUCT_MEDIA) as PM on PRODUCT.ID = PM.PRODUCT_ID where PRODUCT.ID = ? ';

          tx.executeSql(run_sql, [params.product_id],
            (tx, res) => {

              var record = res.rows.item(0);
              this.checkUndefined(record);
              var ret = {
                status: 'Y',
                msg: '',
                data: {
                  id: record.PRODUCT_ID,
                  code: record.CODE,
                  gift: record.GIFT,
                  //shop_name
                  category_ids: record.CATEGORY_IDS.substring(1, record.CATEGORY_IDS.length - 1).split(","),
                  name: record['NAME_' + params.locale],
                  name_en_us: record.NAME_EN_US,
                  name_zh_hk: record.NAME_ZH_HK,
                  name_zh_cn: record.NAME_ZH_CN,

                  highlight: record['HIGHLIGHT_' + params.locale],
                  highlight_en_us: record.HIGHLIGHT_EN_US,
                  highlight_zh_hk: record.HIGHLIGHT_ZH_HK,
                  highlight_zh_cn: record.HIGHLIGHT_ZH_CN,

                  content: record['CONTENT_' + params.locale],
                  content_en_us: record.CONTENT_EN_US,
                  content_zh_hk: record.CONTENT_ZH_HK,
                  content_zh_cn: record.CONTENT_ZH_CN,
                  photos: [],
                  currency: record.CURRENCY,
                  price: record.OFF_PRICE,
                  //specifications : offline.getProductSpec(record.ID),
                  //original_price : offline.getProductPrice(record.ID, record.CURRENCY),
                  qty: record.QTY,
                  sorting: record.SORTING,
                  enabled: record.ENABLED,
                  enable_for_pos: record.ENABLE_FOR_POS,
                  show_price: record.SHOP_PRICE,
                  remark: record.REMARK,
                  specifications: JSON.parse(record.SPEC_FOR_OFFLINE),
                  addons: JSON.parse(record.ADDONS),
                  department_id: record.DEPARTMENT_ID,

                }
              };
              for (var i = 0; i < res.rows.length; i++) {

                var record = res.rows.item(i);
                var photo_url = this.helperService.getRootPath + this.secondPath + record.ID;

                ret.data.photos.push(photo_url);

              }
              console.log('getProductDetail completed');
              resolve(ret);
            },
            (tx, err) => {
              console.log('db execute fail' + err);
              reject(err);
            });
        });
      }, (err) => {
        console.log('sqlite create fail' + err);
        reject(err);
      });
    });
  }


  /**************************************************
  // offline: getSKUInfo
  **************************************************/
  getSKUInfo(params) {
    return new Promise((resolve, reject) => {
      this.sqlite.create({
        name: this.activate.prefix + this.activate.path + '.db',
        location: 'default'
      }).then((db: SQLiteObject) => {
        db.transaction((tx) => {

          var sku = '';
          if (typeof params.spec != "undefined") {

            sku = this.encodePreSku(params.spec);

          }
          sku = sku + 'P' + params.product_id.toString(16);
          if (typeof params.sku_no != "undefined") {
            sku = params.sku_no;
          }
          //console.log(sku);

          var run_sql = 'SELECT * from ((select * from PRODUCT_SKU_INFO where SKU_NO = ?) as F_SKU left join (select ID, NAME_EN_US, NAME_ZH_HK, NAME_ZH_CN from PRODUCT) as SHORT_P on F_SKU.PRODUCT_ID = SHORT_P.ID) as PSI left join (select * from PRODUCT_STOCK where PRODUCT_STOCK.WAREHOUSE_ID = ?) as F_PS on F_PS.SKU_NO = PSI.SKU_NO';

          tx.executeSql(run_sql, [sku, params.warehouse_id],
            (tx, res) => {

              var record = res.rows.item(0);
              this.checkUndefined(record);

              var ret = {
                status: 'Y',
                msg: '',
                product_id: record.PRODUCT_ID,
                product_name: record['NAME_' + params.locale],
                remarks: '------',
                reserved_amount: 0,
                local_qty: this.emptyTozero(record.QTY),
                local_pending_in: this.emptyTozero(record.PENDING_IN),
                local_pending_out: this.emptyTozero(record.PENDING_OUT),
                local_warehouse_id: this.emptyTozero(record.WAREHOUSE_ID),
                local_warehouse_name: 'Offline',
                local_warehouse_location: '',
                global_qty: this.emptyTozero(record.QTY),
                price: record.PRICE,
                original_price: record.ORIGINAL_PRICE,
                data: []
              };
              //console.log(ret);

              console.log('getSkuInfo completed');
              resolve(ret);
            },
            (tx, err) => {
              console.log('db execute fail' + err);
              reject(err);
            });
        });
      }, (err) => {
        console.log('sqlite create fail' + err);
        reject(err);
      });
    });
  }


  /**************************************************
  // offline: newCart
  **************************************************/
  newCart(params) {
    return new Promise((resolve, reject) => {
      this.sqlite.create({
        name: this.activate.prefix + this.activate.path + '.db',
        location: 'default'
      }).then((db: SQLiteObject) => {
        db.transaction((tx) => {

          var time_now = new Date();
          var timestamp = time_now.getTime();
          var beautiful_time = '' + time_now.getFullYear() + '-' + (time_now.getMonth() + 1) + '-' + time_now.getDate() + ' ' + time_now.getHours() + ':' + time_now.getMinutes() + ':' + time_now.getSeconds();
          if (typeof params.user_id != "undefined" && params.user_id != null) {
            var run_sql = 'SELECT * FROM MEMBER_PROFILE where USER_ID = ?';
            tx.executeSql(run_sql, [params.user_id],
              (tx, member_res_original) => {
                if (member_res_original.rows.length != 0) {
                  var ret = {
                    status: 'Y',
                    msg: ''
                  }
                  var member_res = member_res_original.rows.item(0);
                  this.checkUndefined(member_res);
                  //new cart
                  run_sql = 'INSERT INTO INVOICE (CREATE_DATE, UPDATE_DATE, INVOICE_DATE, STATUS, BILLING_FIRST_NAME, BILLING_LAST_NAME, GENDER, CHECK_OUT_WAREHOUSE_ID, USER_ID, BILLING_EMAIL, BILLING_COUNTRY_CODE, BILLING_MOBILE, ITEM_TOTAL, FB_TABLE, PAYED_AMOUNT,CARRY_UP,OCTOPUS_INFO) VALUES (?,?,?, ?, ?, ?,?,?,?, ?, ?, ?,0,0,0,0,?)';

                  tx.executeSql(run_sql, [timestamp, timestamp, beautiful_time, 0, member_res.FIRST_NAME, member_res.LAST_NAME, member_res.GENDER, params.check_out_warehouse_id, params.user_id, member_res.EMAIL, member_res.COUNTRY, member_res.MOBILE, '""'],
                    (tx, update_res) => {
                      var ret = {
                        status: 'Y',
                        msg: '',
                        invoice_id: tx.insertId,
                        billing_last_name: member_res.LAST_NAME,
                        gender: member_res.GENDER,
                      };


                      resolve(ret);

                    },
                    (tx, err) => {
                      reject(err);
                    });
                }
                else {
                  ret = {
                    status: 'E',
                    msg: 'no such member'
                  }
                  resolve(ret);
                }

              },
              (tx, err) => {
                reject(err);
              });

          }
          else {
            var run_sql = 'INSERT INTO INVOICE (CREATE_DATE, UPDATE_DATE, INVOICE_DATE, STATUS, BILLING_LAST_NAME, GENDER, CHECK_OUT_WAREHOUSE_ID, FB_TABLE, ITEM_TOTAL, PAYED_AMOUNT, CARRY_UP,OCTOPUS_INFO) VALUES (?, ?,?,?,?, ?, ?,0,0,0,0,?)';
            tx.executeSql(run_sql, [timestamp, timestamp, beautiful_time, 0, params.billing_last_name, params.gender, params.check_out_warehouse_id, '""'],
              (tx, res) => {
                var ret = {
                  status: 'Y',
                  msg: '',
                  invoice_id: tx.insertId,
                  billing_last_name: params.billing_last_name,
                  gender: params.gender,
                };


                resolve(ret);

              },
              (tx, err) => {
                reject(err);
              });
          }

        });
      }, (err) => {
        console.log('sqlite create fail' + err);
        reject(err);
      });
    });
  }


  /**************************************************
  // offline: setCart
  **************************************************/
  setCart(params) {
    return new Promise((resolve, reject) => {

      switch (params.action) {
        case 'read':
          this.getInvoiceDetail({

            invoice_id: params.invoice_id,
            locale: params.locale,
            fb_table: params.table_num

          }).then(function (ret) {

            resolve(ret);

          });
          break;
        case 'add':
          this.addToCart({

            params: params



          }).then(function (ret_add) {

            this.getInvoiceDetail({

              invoice_id: params.invoice_id,
              locale: params.locale

            }).then(function (ret) {
              ret.item_id = ret_add['item_id'];
              resolve(ret);

            });

          });


          break;
        case 'remove':
          this.removeFromCart({

            params: params



          }).then(function (ret) {

            this.getInvoiceDetail({

              invoice_id: params.invoice_id,
              locale: params.locale

            }).then(function (ret) {

              resolve(ret);

            });

          });
          break;
        case 'empty':
          this.EmptyCart({

            params: params



          }).then(function (ret) {

            this.getInvoiceDetail({

              invoice_id: params.invoice_id,
              locale: params.locale

            }).then(function (ret) {

              resolve(ret);

            });

          });
          break;
        case 'delete':
          this.deleteCart({

            params: params



          }).then(function (ret) {

            resolve(ret);


          });

          break;
        case 'address':

          this.addressCart({

            params: params



          }).then(function (ret) {

            this.getInvoiceDetail({

              invoice_id: params.invoice_id,
              locale: params.locale

            }).then(function (ret) {

              resolve(ret);

            });

          });


          break;
        case 'serve':

          this.serveCart({
            params: params
          }).then(function (ret) {
            console.log(ret);
            resolve(ret);

          });


          break;
      }

    });
  }


  /**************************************************
  // offline: serveCart
  **************************************************/
  serveCart(o_params) {
    return new Promise((resolve, reject) => {
      this.sqlite.create({
        name: this.activate.prefix + this.activate.path + '.db',
        location: 'default'
      }).then((db: SQLiteObject) => {
        db.transaction((tx) => {

          var time_now = new Date();
          var timestamp = time_now.getTime();
          var params = o_params.params;
          if (typeof params.served_item == "undefined") {
            var ret = {
              status: 'E',
              payed: 'E',
              msg: 'Data Empty'
            };
            resolve(ret);

          }
          var served_items = JSON.parse(params.served_item);
          var count = 0;
          for (var i = 0; i < served_items.length; i++) {
            var run_sql = 'UPDATE "INVOICE_ITEM" set "SERVED" = ?, "UPDATE_DATE" = ? where ID = ?';
            tx.executeSql(run_sql, [served_items[i].served, timestamp, served_items[i].item_id],
              (tx, update_res) => {
                count++;
                if (served_items.length == count) {
                  var ret = {
                    status: 'Y',
                    payed: 'E',
                    msg: 'SUCCESS'
                  }
                  run_sql = 'SELECT * from INVOICE left join (select INVOICE_ID, count(*) as ITEM_COUNT , sum(UNIT_PRICE*QTY) as ITEM_TOTAL, count(case when SERVED = 1 AND QTY > 0 then 1 else null end) as SERVED_QTY, count(case when QTY > 0 then 1 else null end) as CC_QTY  from INVOICE_ITEM group by INVOICE_ID) as IITEM on IITEM.INVOICE_ID = INVOICE.ID where INVOICE.ID = ? and INVOICE.STATUS = ?';
                  tx.executeSql(run_sql, [params.invoice_id, 2],
                    (tx, res) => {

                      if (res.rows.length != 0) {
                        var record = res.rows.item(0);
                        this.checkUndefined(record);
                        if (this.emptyTozero(record.SERVED_QTY) == this.emptyTozero(record.CC_QTY)) {
                          this.confirmPayment({

                            invoice_id: params.invoice_id,
                            all_served: 'y',

                          }).then(function (ret_c) {
                            ret.payed = 'Y';
                            resolve(ret);

                          });
                        } else {
                          resolve(ret);
                        }

                      } else {
                        resolve(ret);
                      }

                    },
                    (tx, err) => {
                      reject(err);
                    });
                }

              },
              (tx, err) => {
                reject(err);
              });
          }

        });
      }, (err) => {
        console.log('sqlite create fail' + err);
        reject(err);
      });
    });
  }

  /**************************************************
  // offline: addToCart
  **************************************************/
  addToCart(o_params) {
    return new Promise((resolve, reject) => {
      this.sqlite.create({
        name: this.activate.prefix + this.activate.path + '.db',
        location: 'default'
      }).then((db: SQLiteObject) => {
        db.transaction((tx) => {

          var time_now = new Date();
          var timestamp = time_now.getTime();
          var params = o_params.params;
          var sku = '';
          if (typeof params.spec != "undefined") {

            sku = this.encodePreSku(params.spec);
          }
          sku = sku + 'P' + params.product_id.toString(16);
          if (typeof params.sku_no != "undefined") {
            sku = params.sku_no;
          }

          var add_on_json = '[]';
          var add_on_price = 0;

          if (typeof params.addons != "undefined") {
            add_on_json = params.addons;
            var addons_array = JSON.parse(add_on_json);
            for (var r = 0; r < addons_array.length; r++) {
              add_on_price = add_on_price + parseFloat(addons_array[r].unit_price) * parseFloat(addons_array[r].qty);
            }


          }

          var run_sql = 'SELECT * FROM "PRODUCT_SKU_INFO" WHERE "SKU_NO" = ?';

          tx.executeSql(run_sql, [sku],
            (tx, find_sku_res) => {

              if (find_sku_res.rows.length != 0) {
                var sku_inf = find_sku_res.rows.item(0);
                var run_sql = 'SELECT * FROM "INVOICE_ITEM" WHERE "INVOICE_ID" = ? AND "PRODUCT_ID" = ? and "SKU_NO" = ?';
                var ret = {
                  status: 'Y',
                  msg: '',
                  item_id: 0
                }
                tx.executeSql(run_sql, [params.invoice_id, params.product_id, sku],
                  (tx, res) => {

                    run_sql = 'INSERT INTO "INVOICE_ITEM" (CREATE_DATE, UPDATE_DATE, INVOICE_ID, PRODUCT_ID, UNIT_PRICE, QTY, SKU_NO, STATUS, ADDONS) VALUES(?,?,?,?,?,?,?,?,?)';

                    tx.executeSql(run_sql, [timestamp, timestamp, params.invoice_id, params.product_id, (parseFloat(sku_inf.PRICE) + add_on_price), params.qty, sku, 0, add_on_json],
                      (tx, add_res) => {
                        var ret = {
                          status: 'Y',
                          msg: '',
                          item_id: tx.insertId
                        }

                        resolve(ret);

                      },
                      (tx, err) => {
                        reject(err);
                      });

                  },
                  (tx, err) => {
                    reject(err);
                  });
              }
              else {
                var ret = {
                  status: 'E',
                  msg: 'sku not found',
                  item_id: 0
                }
                resolve(ret);
              }

            },
            (tx, err) => {
              console.log('db execute fail' + err);
              reject(err);
            });
        });
      }, (err) => {
        console.log('sqlite create fail' + err);
        reject(err);
      });
    });
  }


  /**************************************************
  // offline: EmptyCart
  **************************************************/
  EmptyCart(o_params) {
    return new Promise((resolve, reject) => {
      this.sqlite.create({
        name: this.activate.prefix + this.activate.path + '.db',
        location: 'default'
      }).then((db: SQLiteObject) => {
        db.transaction((tx) => {

          var time_now = new Date();
          var timestamp = time_now.getTime();
          var params = o_params.params;

          var run_sql = 'SELECT * FROM "INVOICE_ITEM" WHERE "INVOICE_ID" = ' + params.invoice_id;

          tx.executeSql(run_sql, [],
            (tx, res) => {
              var ret = {
                status: 'Y',
                msg: ''
              }

              if (res.rows.length != 0) {

                for (var i = 0; i < res.rows.length; i++) {
                  var record = res.rows.item(i);
                  this.checkUndefined(record);
                  if (record.UPDATE_DATE == record.CREATE_DATE) {
                    tx.executeSql('DELETE FROM "INVOICE_ITEM" where ID = ?', [record.ID],
                      (tx, update_res) => {
                        resolve(ret);

                      },
                      (tx, err) => {
                        reject(err);
                      });
                  }
                  else {

                    tx.executeSql('UPDATE "INVOICE_ITEM" SET QTY = 0 where ID = ?', [record.ID],
                      (tx, update_res) => {
                        resolve(ret);

                      },
                      (tx, err) => {
                        reject(err);
                      });
                  }
                }


              } else {
                ret.status = 'E';
                resolve(ret);
              }

            },
            (tx, err) => {
              console.log('db execute fail' + err);
              reject(err);
            });
        });
      }, (err) => {
        console.log('sqlite create fail' + err);
        reject(err);
      });
    });
  }


  /**************************************************
  // offline: removeFromCart
  **************************************************/
  removeFromCart(o_params) {
    return new Promise((resolve, reject) => {
      this.sqlite.create({
        name: this.activate.prefix + this.activate.path + '.db',
        location: 'default'
      }).then((db: SQLiteObject) => {
        db.transaction((tx) => {

          var time_now = new Date();
          var timestamp = time_now.getTime();
          var params = o_params.params;
          var sku = '';
          if (typeof params.spec != "undefined") {

            sku = this.encodePreSku(params.spec);
          }
          sku = sku + 'P' + params.product_id.toString(16);
          if (typeof params.sku_no != "undefined") {
            sku = params.sku_no;
          }

          var run_sql = 'SELECT * FROM "PRODUCT_SKU_INFO" WHERE "SKU_NO" = ?';

          tx.executeSql(run_sql, [sku],
            (tx, find_sku_res) => {

              var ret = {
                status: 'Y',
                msg: ''
              }
              if (find_sku_res.rows.length != 0) {
                var sku_inf = find_sku_res.rows.item(0);
                var exec_sql = 'SELECT * FROM "INVOICE_ITEM" WHERE "INVOICE_ID" = ' + params.invoice_id + ' AND "PRODUCT_ID" = ' + params.product_id + ' and "SKU_NO" = ' + sku + '';
                if (typeof params.item_id != "undefined") {
                  exec_sql = 'SELECT * FROM "INVOICE_ITEM" WHERE "ID" = ' + params.item_id;
                }
                tx.executeSql(exec_sql, [],
                  (tx, res) => {

                    if (res.rows.length != 0) {

                      var record = res.rows.item(0);
                      this.checkUndefined(record);

                      if (record.UPDATE_DATE == record.CREATE_DATE) {

                        var run_sql = 'DELETE FROM "INVOICE_ITEM" where ID = ?';
                        tx.executeSql(run_sql, [record.ID],
                          (tx, update_res) => {
                            resolve(ret);
                          },
                          (tx, err) => {
                            reject(err);
                          });
                      }
                      else {
                        var run_sql = 'UPDATE "INVOICE_ITEM" SET QTY = 0 where ID = ?';
                        tx.executeSql(run_sql, [record.ID],
                          (tx, update_res) => {
                            resolve(ret);

                          },
                          (tx, err) => {
                            reject(err);
                          });
                      }
                    }
                    else {
                      ret.status = 'E';
                      resolve(ret);
                    }
                  },
                  (tx, err) => {
                    reject(err);
                  });
              }
              else {
                ret.status = 'E';
                resolve(ret);
              }

            },
            (tx, err) => {
              console.log('db execute fail' + err);
              reject(err);
            });
        });
      }, (err) => {
        console.log('sqlite create fail' + err);
        reject(err);
      });
    });
  }


  /**************************************************
  // offline: deleteCart
  **************************************************/
  deleteCart(o_params) {
    return new Promise((resolve, reject) => {
      this.sqlite.create({
        name: this.activate.prefix + this.activate.path + '.db',
        location: 'default'
      }).then((db: SQLiteObject) => {
        db.transaction((tx) => {

          var time_now = new Date();
          var timestamp = time_now.getTime();
          var params = o_params.params;

          var run_sql = 'SELECT * FROM "INVOICE_ITEM" WHERE "UPDATE_DATE" != "CREATE_DATE"';

          tx.executeSql(run_sql, [],
            (tx, cc_res) => {
              if (cc_res.rows.length != 0) {
                var ret = {
                  status: 'E',
                  msg: 'ITEM SERVED, CANT DELETE CART'
                }
                resolve(ret);
              }
              else {
                run_sql = 'DELETE FROM "INVOICE_ITEM" WHERE "INVOICE_ID" = ?';
                tx.executeSql(run_sql, [params.invoice_id],
                  (tx, update_res) => {
                    run_sql = 'DELETE FROM "INVOICE" WHERE "ID" = ?';
                    tx.executeSql(run_sql, [params.invoice_id],
                      (tx, update_res) => {
                        var ret = {
                          status: 'Y',
                          msg: ''
                        }
                        run_sql = 'DELETE FROM "INVOICE_CHARGES" where INVOICE_ID = ?';
                        tx.executeSql(run_sql, [params.invoice_id],
                          (tx, update_res) => {
                            resolve(ret);
                          },
                          (tx, err) => {
                            reject(err);
                          });
                      },
                      (tx, err) => {
                        reject(err);
                      });
                  },
                  (tx, err) => {
                    reject(err);
                  });
              }
            },
            (tx, err) => {
              console.log('db execute fail' + err);
              reject(err);
            });
        });
      }, (err) => {
        console.log('sqlite create fail' + err);
        reject(err);
      });
    });
  }

  /**************************************************
  // offline: addressCart
  **************************************************/
  addressCart(o_params) {
    return new Promise((resolve, reject) => {
      this.sqlite.create({
        name: this.activate.prefix + this.activate.path + '.db',
        location: 'default'
      }).then((db: SQLiteObject) => {
        db.transaction((tx) => {
          var time_now = new Date();
          var timestamp = time_now.getTime();
          var params = o_params.params;
          this.checkUndefined(params);
          //console.log('in address');
          //console.log(params);
          var b_fn = params.billing_first_name;
          var b_ln = params.billing_last_name;
          var b_country_code = params.billing_country_code;
          var b_mobile = params.billing_mobile;
          var b_email = params.billing_email;
          var pick_warehouse_id = params.pick_up_warehouse_id;
          if (params.delivery_type == 'pick up') {
            b_fn = params.pick_up_first_name;
            b_ln = params.pick_up_last_name;
            b_country_code = params.pick_up_country_code;
            b_mobile = params.pick_up_mobile;
            b_email = params.pick_up_email;
          }

          var run_sql = 'UPDATE "INVOICE" set BILLING_FIRST_NAME = ?, BILLING_LAST_NAME = ?, BILLING_EMAIL = ?, BILLING_COUNTRY_CODE = ?, BILLING_MOBILE = ?, REMARK = ?, DELIVERY_TYPE = ?, PAYMENT_TYPE = ?, PAYED_AMOUNT = ?, PICK_UP_WAREHOUSE_ID = ?, PICK_UP_SITE = ? where ID = ?';
          tx.executeSql(run_sql, [b_fn, b_ln, b_email, b_country_code, b_mobile, params.remark, params.delivery_type, params.payment_type, params.payed_amount, pick_warehouse_id, params.pick_up_site, params.invoice_id],
            (tx, res) => {
              var ret = {
                status: 'Y',
                msg: ''
              }
              run_sql = 'DELETE FROM "INVOICE_CHARGES" where INVOICE_ID = ?';

              tx.executeSql(run_sql, [params.invoice_id],
                (tx, res) => {
                  var ret = {
                    status: 'Y',
                    msg: '',
                  }

                  if (typeof params.invoice_charges != "undefined") {
                    var charge_array = JSON.parse(params.invoice_charges);
                    for (var c = 0; c < charge_array.length; c++) {

                      var run_sql = 'INSERT INTO "INVOICE_CHARGES" (CREATE_DATE, UPDATE_DATE, INVOICE_ID, NAME_EN_US, NAME_ZH_HK, NAME_ZH_CN, SIGN, TYPE,VALUE_TYPE,VALUE) VALUES(?,?,?,?,?,?,?,?,?,?)';
                      tx.executeSql(run_sql, [timestamp, timestamp, params.invoice_id, charge_array[c].title_EN_US, charge_array[c].title_ZH_HK, charge_array[c].title_ZH_CN, charge_array[c].sign, charge_array[c].type, charge_array[c].value_type, charge_array[c].value],
                        (tx, charge_res) => {
                          if (c == charge_array.length) {
                            resolve(ret);
                          }

                        },
                        (tx, err) => {
                          reject(err);
                        });

                    }
                    if (charge_array.length == 0) {
                      resolve(ret);
                    }
                  }
                  else {
                    resolve(ret);
                  }

                },
                (tx, err) => {
                  reject(err);
                });
            },
            (tx, err) => {
              console.log('db execute fail' + err);
              reject(err);
            });
        });
      }, (err) => {
        console.log('sqlite create fail' + err);
        reject(err);
      });
    });
  }

  /**************************************************
  // offline: checkOut
  **************************************************/
  checkOut(params) {
    return new Promise((resolve, reject) => {
      this.sqlite.create({
        name: this.activate.prefix + this.activate.path + '.db',
        location: 'default'
      }).then((db: SQLiteObject) => {
        db.transaction((tx) => {

          var time_now = new Date();
          var timestamp = time_now.getTime();
          this.checkUndefined(params);
          console.log('in checkout');
          var b_fn = params.billing_first_name;
          var b_ln = params.billing_last_name;
          var b_country_code = params.billing_country_code;
          var b_mobile = params.billing_mobile;
          var b_email = params.billing_email;
          var pick_warehouse_id = params.pick_up_warehouse_id;
          if (params.delivery_type == 'pick up') {
            b_fn = params.pick_up_first_name;
            b_ln = params.pick_up_last_name;
            b_country_code = params.pick_up_country_code;
            b_mobile = params.pick_up_mobile;
            b_email = params.pick_up_email;
          }
          var yyyy = time_now.getFullYear().toString();
          yyyy = yyyy.slice(-2);

          var mm = '00' + (time_now.getMonth() + 1).toString(); // getMonth() is zero-based
          mm = mm.slice(-2);
          var dd = '00' + time_now.getDate().toString();
          dd = dd.slice(-2);
          var apid = '0000' + params.invoice_id;
          apid = apid.slice(-4);

          var invoice_no = 'OFFLINE-' + yyyy + mm + dd + apid;

          var all_served = 'n';
          if (typeof params.all_served != "undefined" && params.all_served == 'y') {
            all_served = 'y';
          }
          var octo = '""';
          if (typeof params.octopus_info != "undefined") {
            octo = params.octopus_info;
          }
          var qr = new QRious({ value: invoice_no });
          var invoice_no_qr = qr.toDataURL();
          console.log('before sql');

          var run_sql = 'UPDATE "INVOICE" set BILLING_FIRST_NAME = ?, BILLING_LAST_NAME = ?, BILLING_EMAIL = ?, BILLING_COUNTRY_CODE = ?, BILLING_MOBILE = ?, REMARK = ?, DELIVERY_TYPE = ?, PAYMENT_TYPE = ?, PAYED_AMOUNT = ?, PAY_METHOD = ?, INVOICE_NO = ?, PICK_UP_WAREHOUSE_ID = ?, PICK_UP_SITE = ?, OCTOPUS_INFO = ? where ID = ?';

          tx.executeSql(run_sql, [b_fn, b_ln, b_email, b_country_code, b_mobile, params.remark, params.delivery_type, params.payment_type, params.payed_amount, params.pay_method, invoice_no, pick_warehouse_id, params.pick_up_site, octo, params.invoice_id],
            (tx, res) => {
              var ret = {
                status: 'Y',
                msg: '',
                invoice_id: params.invoice_id,
                invoice_no: invoice_no,
                ticket_num: apid,
                pdf: this.pdfPath,
                data: []
              }
              run_sql = 'DELETE FROM "INVOICE_CHARGES" where INVOICE_ID = ?';
              tx.executeSql(run_sql, [params.invoice_id],
                (tx, res) => {
                  if (typeof params.invoice_charges != "undefined") {
                    var charge_array = JSON.parse(params.invoice_charges);
                    for (var c = 0; c < charge_array.length; c++) {
                      var run_sql = 'INSERT INTO "INVOICE_CHARGES" (CREATE_DATE, UPDATE_DATE, INVOICE_ID, NAME_EN_US, NAME_ZH_HK, NAME_ZH_CN, SIGN, TYPE,VALUE_TYPE,VALUE) VALUES(?,?,?,?,?,?,?,?,?,?)';
                      tx.executeSql(run_sql, [timestamp, timestamp, params.invoice_id, charge_array[c].title_EN_US, charge_array[c].title_ZH_HK, charge_array[c].title_ZH_CN, charge_array[c].sign, charge_array[c].type, charge_array[c].value_type, charge_array[c].value],
                        (tx, charge_res) => {
                          if (c == charge_array.length) {

                            this.getInvoiceDetail(params).then(function (detail_ret) {
                              //console.log(JSON.stringify(detail_ret));
                              ret.data = detail_ret['data'];

                              var products = [];
                              var i = 1;

                              for (let p of detail_ret['data'].products) {
                                var dis = '';
                                if ((p.o_price - p.unit_price) / p.o_price == 0) {
                                  dis = '- ';
                                } else {
                                  dis = (p.o_price - p.unit_price) / p.o_price + '%';
                                }
                                products.push({
                                  ITEM_NO: i,
                                  PRODUCT_CODE: p.code,
                                  PRODUCT_NAME: p.name,
                                  QTY: p.buy_qty,
                                  UNIT_PRICE: p.unit_price,
                                  DISCOUNT: dis,
                                  SUB_TOTAL: '$' + p.sub_total.toFixed(2)
                                });
                                i++;
                              }

                              var charges = [];
                              for (let c of detail_ret['data'].invoice_charges) {
                                var charge_value = '';
                                if (c.sign == '+') {
                                  if (c.value_type == 'value') {
                                    charge_value = '$' + c.value.toFixed(2);
                                  } else {
                                    charge_value = c.value.toFixed(2) + '%';
                                  }
                                } else {
                                  if (c.value_type == 'value') {
                                    charge_value = '($' + c.value.toFixed(2) + ')';
                                  } else {
                                    charge_value = '(' + c.value.toFixed(2) + '%)';
                                  }
                                }
                                charges.push({
                                  CHARGE_NAME: c.title_EN_US,
                                  CHARGE_VALUE: charge_value,
                                  CHARGE_VALUE_TYPE: c.value_type,
                                  CHARGE_SIGN: c.sign
                                });
                              }
                              if (typeof params.confirm != "undefined" && params.confirm == 'y') {
                                this.confirmPayment({

                                  invoice_id: params.invoice_id,
                                  all_served: all_served,

                                }).then(function (ret_c) {

                                  resolve(ret);

                                });
                              } else {
                                resolve(ret);
                              }
                              //resolve(ret);

                            });
                          }

                        },
                        (tx, err) => {
                          reject(err);
                        });
                    }
                    if (charge_array.length == 0) {

                      this.getInvoiceDetail(params).then(function (detail_ret) {
                        //console.log(JSON.stringify(detail_ret));
                        ret.data = detail_ret['data'];

                        var products = [];
                        var i = 1;
                        for (let p of detail_ret['data'].products) {
                          var dis = '';
                          if ((p.o_price - p.unit_price) / p.o_price == 0) {
                            dis = '- ';
                          } else {
                            dis = (p.o_price - p.unit_price) / p.o_price + '%';
                          }
                          products.push({
                            ITEM_NO: i,
                            PRODUCT_CODE: p.code,
                            PRODUCT_NAME: p.name,
                            QTY: p.buy_qty,
                            UNIT_PRICE: p.unit_price,
                            DISCOUNT: dis,
                            SUB_TOTAL: '$' + p.sub_total.toFixed(2)
                          });
                          i++;
                        }

                        var charges = [];
                        for (let c of detail_ret['data'].invoice_charges) {
                          var charge_value = '';
                          if (c.sign == '+') {
                            if (c.value_type == 'value') {
                              charge_value = '$' + c.value.toFixed(2);
                            } else {
                              charge_value = c.value.toFixed(2) + '%';
                            }
                          } else {
                            if (c.value_type == 'value') {
                              charge_value = '($' + c.value.toFixed(2) + ')';
                            } else {
                              charge_value = '(' + c.value.toFixed(2) + '%)';
                            }
                          }
                          charges.push({
                            CHARGE_NAME: c.title_EN_US,
                            CHARGE_VALUE: charge_value,
                            CHARGE_VALUE_TYPE: c.value_type,
                            CHARGE_SIGN: c.sign
                          });
                        }

                        if (typeof params.confirm != "undefined" && params.confirm == 'y') {
                          this.confirmPayment({

                            invoice_id: params.invoice_id,
                            all_served: all_served,

                          }).then(function (ret_c) {

                            resolve(ret);

                          });
                        } else {
                          resolve(ret);
                        }


                      });
                    }
                  }
                  else {
                    this.getInvoiceDetail(params).then(function (detail_ret) {
                      //console.log(JSON.stringify(detail_ret));
                      ret.data = detail_ret['data'];

                      var products = [];
                      var i = 1;
                      for (let p of detail_ret['data'].products) {
                        var dis = '';
                        if ((p.o_price - p.unit_price) / p.o_price == 0) {
                          dis = '- ';
                        } else {
                          dis = (p.o_price - p.unit_price) / p.o_price + '%';
                        }
                        products.push({
                          ITEM_NO: i,
                          PRODUCT_CODE: p.code,
                          PRODUCT_NAME: p.name,
                          QTY: p.buy_qty,
                          UNIT_PRICE: p.unit_price,
                          DISCOUNT: dis,
                          SUB_TOTAL: '$' + p.sub_total.toFixed(2)
                        });
                        i++;
                      }
                      var charges = [];
                      for (let c of detail_ret['data'].invoice_charges) {
                        var charge_value = '';
                        if (c.sign == '+') {
                          if (c.value_type == 'value') {
                            charge_value = '$' + c.value.toFixed(2);
                          } else {
                            charge_value = c.value.toFixed(2) + '%';
                          }
                        } else {
                          if (c.value_type == 'value') {
                            charge_value = '($' + c.value.toFixed(2) + ')';
                          } else {
                            charge_value = '(' + c.value.toFixed(2) + '%)';
                          }
                        }
                        charges.push({
                          CHARGE_NAME: c.title_EN_US,
                          CHARGE_VALUE: charge_value,
                          CHARGE_VALUE_TYPE: c.value_type,
                          CHARGE_SIGN: c.sign
                        });
                      }

                      if (typeof params.confirm != "undefined" && params.confirm == 'y') {
                        this.confirmPayment({

                          invoice_id: params.invoice_id,
                          all_served: all_served,

                        }).then(function (ret_c) {

                          resolve(ret);

                        });
                      } else {
                        resolve(ret);
                      }


                    });
                  }

                },
                (tx, err) => {
                  reject(err);
                });

            },
            (tx, err) => {
              console.log('db execute fail' + err);
              reject(err);
            });
        });
      }, (err) => {
        console.log('sqlite create fail' + err);
        reject(err);
      });
    });
  }

  /**************************************************
  // offline: confirmPayment
  **************************************************/
  confirmPayment(params) {
    return new Promise((resolve, reject) => {
      this.checkUndefined(params);
      this.sqlite.create({
        name: this.activate.prefix + this.activate.path + '.db',
        location: 'default'
      }).then((db: SQLiteObject) => {
        db.transaction((tx) => {

          var run_sql = 'SELECT * from INVOICE left join (select INVOICE_ID, count(*) as ITEM_COUNT , sum(UNIT_PRICE*QTY) as ITEM_TOTAL, count(case when SERVED = 1 AND QTY > 0 then 1 else null end) as SERVED_QTY, count(case when QTY > 0 then 1 else null end) as CC_QTY  from INVOICE_ITEM group by INVOICE_ID) as IITEM on IITEM.INVOICE_ID = INVOICE.ID where INVOICE.ID = ?';
          tx.executeSql(run_sql, [params.invoice_id],
            (tx, res) => {
              var ret = {
                status: 'Y',
                msg: '',
                data: {
                  count: res.rows.length,
                  list: []
                }
              };
              console.log('before length');
              if (res.rows.length > 0) {
                console.log('after length');
                var record = res.rows.item(0);
                this.checkUndefined(record);
                var serve_qty = this.emptyTozero(record.SERVED_QTY);
                var item_qty = this.emptyTozero(record.CC_QTY);
                var table_num = this.emptyTozero(record.FB_TABLE);

                var delivery_type = "";
                if (record.DELIVERY_TYPE == "") {
                  delivery_type = delivery_type + ", DELIVERY_TYPE = 'direct sales'";
                }

                if (typeof params.all_served != "undefined" && params.all_served == 'y') {
                  serve_qty = item_qty;
                }

                if (serve_qty != item_qty) {

                  if (record.INVOICE_NO == "") {
                    var ret = {
                      status: 'E',
                      msg: 'Please Print First',
                      data: {
                        count: res.rows.length,
                        list: []
                      }
                    };
                    resolve(ret);

                  } else {
                    console.log('before sql');

                    var run_sql = 'UPDATE "INVOICE" set STATUS = ?' + delivery_type + ' where ID = ?';
                    tx.executeSql(run_sql, [2, params.invoice_id],
                      (tx, update_res) => {
                        var ret = {
                          status: 'Y',
                          msg: '',
                          invoice_id: params.invoice_id,
                        }

                        console.log(ret);
                        resolve(ret);

                      },
                      (tx, err) => {
                        reject(err);
                      });

                  }

                }
                else {

                  var run_sql = 'UPDATE "INVOICE" set STATUS = ? where ID = ?';
                  tx.executeSql(run_sql, [5, params.invoice_id],
                    (tx, update_res) => {
                      var ret = {
                        status: 'Y',
                        msg: '',
                        invoice_id: params.invoice_id,
                      }

                      //console.log(ret);
                      resolve(ret);

                    },
                    (tx, err) => {
                      reject(err);
                    });
                }
              } else {
                ret = {
                  status: 'E',
                  msg: 'Invoice Not Found',
                  data: {
                    count: 0,
                    list: []
                  }
                };
                resolve(ret);
              }

            },
            (tx, err) => {
              console.log('db execute fail' + err);
              reject(err);
            });
        });
      }, (err) => {
        console.log('sqlite create fail' + err);
        reject(err);
      });
    });
  }

  /**************************************************
  // offline: getTableInfo phi_0620
  **************************************************/
  getTableInfo(params) {
    return new Promise((resolve, reject) => {
      this.sqlite.create({
        name: this.activate.prefix + this.activate.path + '.db',
        location: 'default'
      }).then((db: SQLiteObject) => {
        db.transaction((tx) => {

          var status_sql = "";

          status_sql = " where INVOICE.STATUS != -1 and INVOICE.STATUS != 5 and INVOICE.FB_TABLE != 0";

          status_sql = status_sql + " order by INVOICE.ID desc";

          var table_total = 50;

          if (typeof params.table_total != "undefined") {
            table_total = params.table_total;
          }

          var table_arr = [];
          for (var a = 0; a < table_total; a++) {
            table_arr.push([]);
          }
          var run_sql = 'SELECT * from INVOICE left join (select INVOICE_ID, count(*) as ITEM_COUNT , sum(UNIT_PRICE*QTY) as ITEM_TOTAL, count(case when SERVED = 1 AND QTY > 0 then 1 else null end) as SERVED_QTY, count(case when QTY > 0 then 1 else null end) as CC_QTY from INVOICE_ITEM group by INVOICE_ID) as IITEM on IITEM.INVOICE_ID = INVOICE.ID' + status_sql;
          tx.executeSql(run_sql, [],
            (tx, res) => {
              var ret = {
                status: 'Y',
                msg: '',
                data: {
                  count: res.rows.length,
                  list: []
                },
                red: [],
                table: table_arr,
              };
              var invoice_charge_index = 0;
              for (var i = 0; i < res.rows.length; i++) {
                var record = res.rows.item(i);
                this.checkUndefined(record);
                var serve_status = this.emptyTozero(record.SERVED_QTY) + '/' + this.emptyTozero(record.CC_QTY);
                var inv_info = {
                  id: record.ID,
                  table_num: this.emptyTozero(record.FB_TABLE),
                  invoice_no: record.INVOICE_NO,
                  item_count: this.emptyTozero(record.ITEM_COUNT),
                  currency: record.CURRENCY,
                  gender: this.emptyTozero(record.GENDER),
                  billing_last_name: record.BILLING_LAST_NAME,
                  price: this.emptyTozero(record.ITEM_TOTAL),
                  status_num: this.emptyTozero(record.STATUS),
                  invoice_date: record.INVOICE_DATE,
                  bill_to: record.BILLING_FIRST_NAME + ' ' + record.BILLING_LAST_NAME,
                  member_mobile: record.BILLING_COUNTRY_CODE + ' ' + record.BILLING_MOBILE,
                  delivery_type: record.DELIVERY_TYPE,
                  check_out_warehouse: this.emptyTozero(record.CHECK_OUT_WAREHOUSE_ID),
                  pick_up_warehouse_id: this.emptyTozero(record.PICK_UP_WAREHOUSE_ID),
                  expected_delivery_date: record.EXPECTED_DELIVERY_DATE,
                  name: 'Offline Visitor',
                  user_id: this.emptyTozero(record.USER_ID),
                  member_id: 0,
                  vip_level: 0,
                  member_profile: [],
                  served_qty: serve_status,
                  sync: this.emptyTozero(record.CARRY_UP),
                };

                var table_id = record.FB_TABLE.replace(/^\D+/g, '');
                if (ret.red.indexOf(table_id) == -1) {
                  ret.red.push(table_id);
                }

                var obj = {
                  table_num: record.FB_TABLE,
                  data: inv_info
                };
                ret.table[table_id - 1].push(obj);

                if (inv_info.bill_to != ' ') {
                  inv_info.name = inv_info.bill_to;
                }


                var iid = record.ID;
                var price = this.emptyTozero(record.ITEM_TOTAL);

                this.calInvoiceNetDisCha(iid, price).then(function (ret) {

                  inv_info.price = ret;

                  if (res.rows.length == i + 1) {

                    resolve(ret);
                  }

                });

              }
              if (0 == ret.data.list.length) {
                //console.log(ret);
                resolve(ret);
              }


            },
            (tx, err) => {
              reject(err);
            });
        });
      }, (err) => {
        console.log('sqlite create fail' + err);
        reject(err);
      });
    });
  }

  /**************************************************
  // offline: calInvoiceNetDisCha phi_0620
  **************************************************/
  calInvoiceNetDisCha(iid, base) {
    return new Promise((resolve, reject) => {
      this.sqlite.create({
        name: this.activate.prefix + this.activate.path + '.db',
        location: 'default'
      }).then((db: SQLiteObject) => {
        db.transaction((tx) => {

          var run_sql = 'SELECT * from INVOICE_CHARGES where INVOICE_ID=?';
          tx.executeSql(run_sql, [iid],
            (tx, inv_charge) => {

              var baseDiscount = 0;
              var baseCharge = 0;
              var baseTotal = base;

              for (var j = 0; j < inv_charge.rows.length; j++) {
                var invoice_charge = inv_charge.rows.item(j);
                this.checkUndefined(invoice_charge);
                if (invoice_charge.VALUE_TYPE == 'percent') {
                  if (invoice_charge.SIGN == '+') {
                    var totalFlag = baseTotal;
                    baseTotal *= 1 + Number(invoice_charge.VALUE) / 100;
                    baseCharge += baseTotal - totalFlag;
                  } else {
                    var totalFlag = baseTotal;
                    baseTotal *= 1 - Number(invoice_charge.VALUE) / 100;
                    baseDiscount += totalFlag - baseTotal;
                  }
                }

              }

              for (var j = 0; j < inv_charge.rows.length; j++) {
                var invoice_charge = inv_charge.rows.item(j);
                this.checkUndefined(invoice_charge);
                if (invoice_charge.VALUE_TYPE != 'percent') {
                  if (invoice_charge.SIGN == '+') {
                    var totalFlag = baseTotal;
                    baseTotal += Number(invoice_charge.VALUE);
                    baseCharge += baseTotal - totalFlag;
                  } else {
                    var totalFlag = baseTotal;
                    baseTotal -= Number(invoice_charge.VALUE);
                    baseDiscount += totalFlag - baseTotal;
                  }
                }

              }
              resolve(baseTotal);

            },
            (tx, err) => {
              console.log('db execute fail' + err);
              reject(err);
            });
        });
      }, (err) => {
        console.log('sqlite create fail' + err);
        reject(err);
      });
    });
  }


  /**************************************************
  // get templates for invoice printing
  **************************************************/
  getInvoiceTemplates(size): any {
    console.log('getting invoice templates');
    return new Promise((resolve, reject) => {
      switch (size) {
        case '80mm':
          this.file.readAsText(this.file.applicationDirectory + 'www/assets/templates/', 'tpl.invoice-pdf.' + size + '.css.html')
            .then((css) => {
              console.log('complete getting css');
              this.file.readAsText(this.file.applicationDirectory + 'www/assets/templates/', 'tpl.invoice-pdf.' + size + '.body.html')
                .then((body) => {
                  console.log('complete getting body');
                  this.file.readAsText(this.file.applicationDirectory + 'www/assets/templates/', 'tpl.invoice-pdf.' + size + '.product-item.html')
                    .then((productItem) => {
                      console.log('complete getting product item');
                      this.file.readAsText(this.file.applicationDirectory + 'www/assets/templates/', 'tpl.invoice-pdf.' + size + '.charge-item.html')
                        .then((chargeItem) => {
                          console.log('complete getting charge item');
                          resolve({
                            css: css,
                            body: body,
                            productItem: productItem,
                            chargeItem: chargeItem
                          });
                        }, (err) => {
                          console.log(err);
                          reject(err);
                        });
                    }, (err) => {
                      console.log(err);
                      reject(err);
                    });
                }, (err) => {
                  console.log(err);
                  reject(err);
                });
            }, (err) => {
              console.log(err);
              reject(err);
            });
          break;

        default:
          reject({
            status: 'E',
            msg: 'INVALID_INVOICE_SIZE'
          });
      }
    });
  }

  /**************************************************
  // get invoice content
  **************************************************/
  getInvoiceHtml(params): any {
    /* conn: {
      size: '80mm', // currently only support 80mm
      pages: [
        {
          COMPANY_LOGO: 'data:image/png;base64',  // company logo in base64 data
          SHOW_COMPANY_LOGO: '',  // '', 'none'. show/hide company logo
          SHOP_TEL: '28119139',  // shop tel no
          SHOP_ADDRESS: '旺角 旺角道 26號',  // shop address
          SHOW_SHOP_INFO: '',  // '', 'none'. show/hide shop info
          INVOICE_TITLE: '收據',  // '收據', '發票'
          SHOW_INVOICE_TITLE: '',  // '', 'none'. show/hide invoice title
          TICKET_TYPE: '檯號',  // '檯號', '堂食'
          TICKET_NO: '1A',  // ticket no. or table no.
          INVOICE_TYPE: '堂食',  // '堂食', '外賣'
          MEMBER_CLASS: '訪客',  // member class
          SHOW_MEMBER_CLASS: '',  // '', 'none'. show/hide member class
          INVOICE_DATE: '2017-06-17 13:38:05',  // invoice date
          INVOICE_NO: '0006',  // invoice no.
          SHOW_INVOICE_NO: '',  // '', 'none'. show/hide invoice no
          SALESMAN: 'arcadmin@phi.me',  // salesman
          SHOW_SALESMAN: '',  // '', 'none'. show/hide salesman
          DEPARTMENT_NAME: '廚房',  // department name
          SHOW_DEPARTMENT_NAME: 'none',  // '', 'none'. show/hide department name
          SHOW_PRICE: '',  // '', 'none'. show/hide price
          PRODUCT_ITEM: [  // products
            {
              PRODUCT_NAME: '士多啤梨奶<br />走甜',
              QTY: '1',
              DISCOUNT: '-',
              SUB_TOTAL: '$36.0',
              SHOW_PRICE: '',  // 'block', 'none'. show/hide price
            }
          ],
          CHARGE_ITEM: [
            {
              CHARGE_NAME: '青春損失費',
              CHARGE_VALUE: '10%',
              CHARGE_AMOUNT: '($100.0)',
            }
          ],
          ITEM_TOTAL: '$130.0',
          GRAND_TOTAL: '$130.0',
          CASH_RECEIVED: '$150.0',
          CHANGES: '$20.0',
          SHOW_PAYMENT_DETAIL: '',
          SHOW_RECEIVE_DETAIL: '',
          DEVICE_NO: 'B028172',
          OCTOPUS_NO: '9987766',
          AMOUNT_DEDUCTED: '$-130.0',
          REMAINING_VALUE: '$999.0',
          DEDUCT_DATE: '2017-06-17 13:38:05',
          SHOW_OCTOPUS: '',
          SHOW_FOOTER: '',
        }
      ]
    } */
    return new Promise((resolve, reject) => {
      this.getInvoiceTemplates(params.size).then((templates) => {
        let htmls = [];

        // regular expression for data replacement
        let translateReg = /{{ '(\w+)' \| TRANSLATE }}/g;
        let dataReg = /{{ '(\w+)' \| DATA }}/g;
        let productItemReg = /{{ '(\w+)' \| PRODUCT_ITEM }}/g;
        let chargeItemReg = /{{ '(\w+)' \| CHARGE_ITEM }}/g;

        // replace content
        for (let i = 0; i < params.pages.length; i++) {
          let html = templates.body.replace(translateReg, (match, param, offset, string) => {
            return this.translate.instant(param);
          });

          html = html.replace(dataReg, (match, param, offset, string) => {
            return params.pages[i][param];
          });

          let products = '';
          for (let item of params.pages[i].PRODUCT_ITEM) {
            products += templates.productItem.replace(productItemReg, (match, param, offset, string) => {
              return item[param];
            });
          }
          html = html.replace(/{{ 'PRODUCT_ITEM' \| TEMPLATE }}/g, products);

          let charges = '';
          for (let j = 0; j < params.pages[i].CHARGE_ITEM.length; j++) {
            charges += templates.chargeItem.replace(chargeItemReg, (match, param, offset, string) => {
              return params.pages[i].CHARGE_ITEM[j][param];
            });
          }
          html = html.replace(/{{ 'CHARGE_ITEM' \| TEMPLATE }}/g, charges);

          htmls.push(html);
        }

        // resolve promise
        resolve({ css: templates.css, htmls: htmls });
      }, (err) => {
        this.helperService.showToast(this.translate.instant(err));
      });
    });
  }

  /**************************************************
  // start socket server for data synchronization
  **************************************************/
  startServer(port) {
    if (this.global.getScope('socketServer') == null) {
      console.log('starting socket server');
      if (this.global.getScope('socketClient') != null) {
        this.disconnectFromServer();
      }

      this.global.setScope('socketServer', {
        socket: cordova.plugins.wsserver,
        port: port,
        clients: []
      });

      let _that = this;

      this.global.getScope('socketServer').socket.start(port, {
        // WebSocket Server handlers
        'onFailure': function (addr, port, reason) {
          console.log('Stopped listening on %s:%d. Reason: %s', addr, port, reason);
          _that.global.clearScope('socketServer');
          // TODO: make more presentable alert message
          alert('Local server stopped. Reason: ' + reason);
        },
        // WebSocket Connection handlers
        'onOpen': function (conn) {
          /* conn: {
          'uuid' : '8e176b14-a1af-70a7-3e3d-8b341977a16e',
          'remoteAddr' : '192.168.1.10',
          'acceptedProtocol' : 'my-protocol-v1',
          'httpFields' : {...},
          'resource' : '/?param1=value1&param2=value2'
          } */
          console.log('A user connected from %s', conn.remoteAddr);
          let socketServer = _that.global.getScope('socketServer');
          socketServer.clients.push(conn);
          _that.global.setScope('socketServer', socketServer);
          // TODO: translation with remote address
          _that.helperService.showToast(_that.translate.instant('CLIENT_CONNECTED'));
        },
        'onMessage': function (conn, msg) {
          console.log(conn + msg);
          alert(msg);
          //                 $rootScope.stopSyncCommand = true;
          //                 var command = angular.fromJson(msg);
          //                 console.log(command);
          //                 // TODO: trigger method with params
          //                 $eval(
          //                   command.method + '(' + command.params + ').then(function(res) { \
          //                     $rootScope.stopSyncCommand = false; \
          //                   }).catch(function(err) { \
          //                     $rootScope.stopSyncCommand = false; \
          //                   }); \
          //                 ');
          // TODO: broadcast to other clients
          for (var i = 0; i < _that.global.getScope('socketServer').clients.length; i++) {
            if (_that.global.getScope('socketServer').clients[i].uuid != conn.uuid) {
              _that.global.getScope('socketServer').socket.send(_that.global.getScope('socketServer').clients[i], msg);
            }
          }
        },
        'onClose': function (conn, code, reason) {
          console.log('A user disconnected from %s', conn.remoteAddr);
          var clients = [];
          for (var i = 0; i < _that.global.getScope('socketServer').clients.length; i++) {
            if (_that.global.getScope('socketServer').clients[i].uuid != conn.uuid) {
              clients.push(_that.global.getScope('socketServer').clients[i]);
            }
          }
          let socketServer = _that.global.getScope('socketServer');
          socketServer.clients = clients;
          _that.global.setScope('socketServer', socketServer);
        },
        // Other options
        //         'origins': [ 'file://' ], // validates the 'Origin' HTTP Header.
        //         'protocols': [ 'ws', 'http' ], // validates the 'Sec-WebSocket-Protocol' HTTP Header.
        'tcpNoDelay': true // disables Nagles algorithm.
      }, function onStart(addr, port) {
        console.log('Listening on %s:%d', addr, port);
        _that.showServerQrCode();
      }, function onDidNotStart(reason) {
        console.log('Did not start. Reason: %s', reason);
        _that.global.clearScope('socketServer');
        // TODO: make more presentable alert message
        alert('Could not start local server. Reason: ' + reason);
      });
    }
  }

  /**************************************************
  // show server QR code for client connection
  **************************************************/
  showServerQrCode() {
    console.log('show server qr code');
    let port = this.global.getScope('socketServer').port;
    // This method is NOT deprecated and NOT uses the getWiFiIPAddress method.
    (<any>window).networkinterface.getIPAddress((ip) => {
      console.log('get intranet ip address: ' + ip);
      var data = JSON.stringify({
        ipAddr: ip,
        port: port
      });
      let modal = this.modalCtrl.create(ServerQrCodePage, { data: data });
      modal.present();
    }, function (err) {
      // window.networkinterface only work in real device
    });
  }

  /**************************************************
  // stop socket server for some reason
  **************************************************/
  stopServer() {
    if (this.global.getScope('socketServer') != null) {
      this.global.getScope('socketServer').socket.stop(function onStop(addr, port) {
        console.log('Stopped listening on %s:%d', addr, port);
        this.global.clearScope('socketServer');
      });
    }
  }

  /**************************************************
  // scan server QR code to connect to socket server
  **************************************************/
  scanServerQrCode() {
    this.barcodeScanner.scan().then((barcodeData) => {
      try {
        let data = JSON.parse(barcodeData.text);
        this.connectToServer(data.ipAddr, data.port);
      }
      catch (err) {
        this.helperService.showToast(this.translate.instant('INVALID_SERVER_QR_CODE'));
      }
    }, (err) => {
      this.helperService.showToast(this.translate.instant('SCANNER_FAIL'));
    });
  }

  /**************************************************
  // connect to socket server for data synchronization
  **************************************************/
  connectToServer(ipAddr, port) {
    if (this.global.getScope('socketClient') == null) {
      console.log('connecting to socket server');
      if (this.global.getScope('socketServer') != null) {
        this.stopServer();
      }

      var socketClient = {
        socket: new $WebSocket('ws://' + ipAddr + ':' + port.toString()),
        ipAddr: ipAddr,
        port: port
      };

      socketClient.socket.onOpen(function () {
        console.log('Connected to %s:%d', this.global.getScope('socketClient').ipAddr, this.global.getScope('socketClient').port);
      });

      socketClient.socket.onMessage((msg) => {
        alert(msg.data);
        //               $rootScope.stopSyncCommand = true;
        //               var command = angular.fromJson(msg.data);
        //               console.log(command);
        //               // TODO: trigger method with params
        //               $eval(
        //                 command.method + '(' + command.params + ').then(function(res) { \
        //                   $rootScope.stopSyncCommand = false; \
        //                 }).catch(function(err) { \
        //                   $rootScope.stopSyncCommand = false; \
        //                 }); \
        //               ');
      }, { autoApply: false });

      socketClient.socket.onError(function (e) {
        console.log('Client socket error:' + e);
      });

      socketClient.socket.onClose(function (msg) {
        this.global.clearScope('socketClient');
      });

      this.global.setScope('socketClient', socketClient);
    }
  }

  /**************************************************
  // disconnect from server for some reason
  **************************************************/
  disconnectFromServer() {
    if (this.global.getScope('socketClient') != null) {
      this.global.getScope('socketClient').socket.close(false);
    }
  };

  /**************************************************
  // synchronize command depends on server / client
  **************************************************/
  syncCommand(command) {
    if (this.global.getScope('socketServer') != null) {
      for (let i = 0; i < this.global.getScope('socketServer').clients.length; i++) {
        this.global.getScope('socketServer').socket.send(this.global.getScope('socketServer').clients[i], command);
      }
    }
    if (this.global.getScope('socketClient') != null) {
      this.global.getScope('socketClient').socket.send(command);
    }
  }

}
